Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Why is there no SQL Command using Variable in ADO NET Source? Expand / Collapse
Posted Sunday, January 11, 2009 10:32 PM


Group: General Forum Members
Last Login: Wednesday, March 23, 2011 5:56 AM
Points: 22, Visits: 50

using SSIS 2008....

I am using a script task to generate a dynamic query which has to be executed on RDS (Relational Database) system using ODBC connection and inserted into a SQL table. The query is executed using the Execute SQL Task but I need to do row by row transformations (like using the script component and data transformation) on the resultset and then insert into a SQL Table.

I am not able to execute the dynamic query in the "ADO NET Source" of the data flow task as the provider doesn't have the "Data Access mode" as "SQL Command from variable" which is provided in the OLE DB Source.

Worst case I will have use Execute SQL Query to insert the dynamic query output into a table in the RDS system and then use this table in the ADO NET source of the data flow task, but I want to avoid this operation of inserting into a table as table creation and inserting records in RDS is quite troublesome.

Is there a way out?? Please suggest.

Thanks in advance.
Post #634472
Posted Monday, January 12, 2009 6:13 AM


Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
You can use expressions to set the command for this data source to the value of your variable.
Post #634610
Posted Monday, June 29, 2009 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 1, 2009 8:51 AM
Points: 1, Visits: 17
Hi, I am struggling with the same problem. How do you set up an expression for the SQLcommand in an ADO.NET source, please?

Thanks in advance,
Post #743658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse