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

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.

Execute Package Task using SSIS Package Store package

From the Execute Package Task Editor, what info do you have to enter to
reference a SSIS Package Store package?
For the Location, I've tried both "SQL Server" and "File" and nothing
works except selecting the actual location from the "File" option.
For the Connection, I've tried the default "Native OLE DB\..." and "..OLE DB
Provider for DTS.." as Provider with no result.
For PackageName, I've tried various combination "\File System\PackageName",
"PackageName", etc... and nothing works.

I'm assuming this task allow for the 3 different ways to store the package (SQL Server/MSDB, File System, Package Store (hybrid)).

Any ideas!!!?

The documentation for the task here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/042d4ec0-0668-401c-bb3a-a25fe2602eac.htm

says that the package can originate only from the File System or MSDB.

You could however use DTExec to fire the package with the /D[ts] package_path
parameter
Allan|||Remember as well that all the package store is the only the logical container for MSDB and the folder location specified in MsDtsSrvr.ini. The thing about it is that the service is aware of this location.
Allan|||The problem with the Package Store is that it is both or "managed" File System. I think someone has submitted a bug report on my behalf for this. Thanks.