error when transfer variable string to OLE DB Data source

SSIS OLE DB Source must have an initial column definition in order to work. When you use 'SQL Command from Variable', you need to provide this column definition.

Let me start with a general application of the concept. Then I will address your particular scenario.

General Case

In some cases, you may have a string variable that has no initial value, and at some point in the process, the value is assigned. In this scenario, an initial value MUST be provided. One way to do this is to use a dummy query that provides the columns and data types.

For example, set the initial value of your variable to something like:

SELECT CAST('' AS VARCHAR(10) AS MyColumn1, 0 AS MyInteger FROM MyTable WHERE 1= 2

This query will never return results, but it provides the column definition that the OLE DB Source needs. At some point before the OLE DB Source executes, you'll need to assign the actual query to the variable.

Specific Case

Your case is similar, but slightly different. In your particular case, you have this query:

SqlString='select * from CustomizedFunction(Argument1,Argument2,Argument3)'

OLE DB Source has no idea what the column definitions are with SELECT *. You need to replace SELECT * with the actual column names and their data types.

SqlString='SELECT CAST(MyColumn1 AS VARCHAR(10)) AS MyColumn1, CAST(MyColumn2 AS INT) AS MyColumn2 FROM CustomizedFunction(Argument1,Argument2,Argument3)'

I am added an updated answer based on a recent experience with this issue.

After the steps for aliasing all the columns (giving each column a column header) we had to go through the additional step of setting 'Validate External Metadata' to false in the advanced editor for the SSIS task. Once that was done, the error was removed for us.

I am leaving this here as additional help for future users that encounter this issue.