Why is there no SQL Command using Variable in ADO NET Source?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Why is there no SQL Command using Variable in...

Why is there no SQL Command using Variable in ADO NET Source?

Rate Topic

Display Mode

Topic Options

Author

Message

sqlbuddy

Posted Sunday, January 11, 2009 10:32 PM

Grasshopper

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

Hi,

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

Michael Earl-395764

Posted Monday, January 12, 2009 6:13 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

You can use expressions to set the command for this data source to the value of your variable.

Post #634610

grobut

Posted Monday, June 29, 2009 8:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 01, 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,
Mikkel

Post #743658

« Prev Topic | Next Topic »

Permissions