Execute SQL from File: How Can I process the record set?

I want to be able to pass the location of a file (contains SQL to be executed) to my package at run time. To do this I was going to override the connection string for the file.

I've created a 'Execute SQL Task' that opens the sql script and stores the full result set into an variable (system.object). I can execute this and it works fine i.e turns green :).

However I can't work out how to get the data back out of the variable. I have found a doc on SQLIS (The ExecuteSQL Task) that explained how to get the data in to a variable but didn't tell me how to process the data afterwards. There is another article on there that shows how to shred a recordset (Shredding a Recordset) but this example uses an OLE DB source and the 'Recordset Destination' object. This would work but but the only options are sql from a variable or the option to type in the command.

I really have two questions here.

1. Using the first method how can I pass the data stored in the variable into a data flow so that I can use it.

2. using the second method, how can I pass the SQL into a variable at runtime from a file?

Has anyone got examples of how they read SQL from a file and process the data without having to hard code the sql or sql file name in the package.

#1) If you have data in an object and want to use it in a data-flow then you're going to need to loop over the records in the object and add them to the pipeline. Its custom source adapter time!!!

Here's how you do it in a script task: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx I dare say you can take this code and adapt it to use it in a script component. I have to say, I haven't actually tried it.

#2) Again you may need a custom/script task to do this. I don't have time to look at this now but I'll try later. It shouldn't be too difficult, just use System.IO namespace.

-Jamie|||To configure the file connection at runtime. You need to use expressions. On the properties of the data flow task select expressions. In here you can set the connection string property of the flat file to the variable (containing a filename).