THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

SQL Task in SSIS with external file

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!

Published Thursday, March 31, 2005 9:44 AM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement