OLE DB Source and SQL Command from variable
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 ------------------------------
March 16th, 2011 3:26am

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/
Free Windows Admin Tool Kit Click here and download it now
March 16th, 2011 3:32am

"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
March 16th, 2011 3:41am

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
Free Windows Admin Tool Kit Click here and download it now
March 16th, 2011 4:12am

"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/
March 16th, 2011 5:23am

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
Free Windows Admin Tool Kit Click here and download it now
March 16th, 2011 1:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics