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

  • 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.

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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply