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

• sqlbuddy

  SSC Veteran

  Points: 288

  More actions

  January 11, 2009 at 10:32 pm

  #125573

  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.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  January 12, 2009 at 6:13 am

  #925210

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

• grobut

  SSC Journeyman

  Points: 77

  More actions

  June 29, 2009 at 8:25 am

  #1016857

  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