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.