Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help