SSIS has a feature I desperately wanted in DTS for years: you can use an external SQL file as a source SQL statement for the SQL Execute Task. Unfortunately this is not available for Data Flow Source component.
I tried using variables to load the SQL command for a Data Flow Source from an external text file, but you need to write a Script Task and load the file content into the variable. I’d like a custom property editor for the Value property of a Variable to insert a multi-line string (as a SELECT statement) – with February CTP you can’t use copy/paste and you need to edit the DTSX in XML to insert the initial SELECT statement into the variable (otherwise you can’t use the designer to define the Data Flow components).
Probably a better approach is to define several stored procedures and write a SQL Execute Task which execute external scripts to define stored procedure and then to use the stored procedure as Data Flow Source text command. I used a similar approach in DTS 2000. The problem is that probably the package doesn’t know that it has an external dependency, so the deployment of external scripts has to be done by hand. But it would be a good solution if a BI solution could deploy into the bin directory each file included in the solution and configured to be deployed. Actually it is possible with VS2005 so I’d like to have this chance in a BI project. Someone know a way to do it? (I already asked on the SSIS newsgroup too).
UPDATE: I just discovered that enabling the “CreateDeploymentUtility” flag into project copies all miscellaneous flag included in the project into the Deployment directory. Exactly what I wanted for deployment!