I have an SSIS Package with a script task and a data flow task. I am trying to set the value of a variable in script task and send it to OLEDB Source. Inside the script task I am setting a variable like
Dts.Variables("SQL").Value = "Select * from TableName"
EvaluateAsExpression is set to true for SQL. But inside the Data Flow task, the OLE DB Source is not accepting this variable for its "SQL Command from variable". In the editor I am getting the following error.


TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

Need to support users over the internet? click here try our remote control online beta

Could you please share your SQL query which you are trying to assign to "SQL" variable.

Let us TRY this |

My Blog ::
http://quest4gen.blogspot.com/


Need to support users over the internet? click here try our remote control online beta

"Select * from Attributes";
With out any parameter. When I try to assign the variable inside the editor, error pops out. I am using SQL Server Business Intelligence Development Studio 2005

There is an amazing pack of free network admin tools. click here to download it

If this is the command then you can directly use it with Data access mode as SQL Command. In your case you need to give a proper value to the variable SQL so that you can configure the data flow task. The script task will populate the variable once it is
executed but you need a proper value for variable SQL at design time. So give the default value to the variable and test the package.Nitesh Rai- Please mark the post as answered if it answers your question

There is an amazing pack of free network admin tools. click here to download it

"Select * from Attributes";
With out any parameter. When I try to assign the variable inside the editor, error pops out. I am using SQL Server Business Intelligence Development Studio 2005

You don't "pass parameters" when you're doing the SQL Command from Variable.
So better go with Nitesh's suggestion.

Let us TRY this |

My Blog ::
http://quest4gen.blogspot.com/


There is an amazing pack of free network admin tools. click here to download it

SQL's EvaluateAsExpression should be set to FALSE. You're not putting an expression into the variable, you're directly assigning a static value through your script.
Second, you need to manually set a "default" value for your SQL variable in the Variables pane, otherwise the OLE DB Source has nothing to use for the SQL statement at design time.



Talk to me now on

Need to support users over the internet? click here try our remote control online beta