SSIS Fact and Myth: Dynamic Selects in SSIS Packages

Fact: The metadata of the SSIS dataflow task cannot be changed at run time (e.g. changing number or name of columns in source components) *

Myth: You always have to use dataflow tasks when moving data with SSIS.


I often see people hitting roadblocks when using SSIS in scenarios where the source and/or target structures needs to change dynamically at run time. You can see an example of that in this post

Based on my experience, some of these scenarios - including the link above -, require minimal or no data transformation, hence making the usage of dataflow tasks a less appealing story.

Let's see an example.

Scenario:
You need to build a process to export data out of a SQL Server Database into flat files. The process should be able to perform a 'data dump' given the table name(s) or given a query(ies).


Solution:
These requirements can be addressed by using few SSIS control flow tasks:




1. An Execute SQL Task to get the detail of the data dump(s): Source DB instance, destination flat file name and path, source table/query and so on. The execute SLQ task runs a query that retrieves a data set that looks like this:




-- Just a collection of hardcoded values to give the idea
-- of using a data driven approach. I would probably create
-- a table for this.
Select 'AdventureWorks2008' DB, 'Sales.Currency' AS TableNameOrQuery, 'C:\Temp\' FilePath, 'table' As ExportType, 'Currency.txt' OutPutFileName, 'MARINERLAPTOP24\SQL2008' SourceServerName
UNION ALL
Select 'AdventureWorks2008' DB, 'Sales.Store' AS TableNameOrQuery, 'C:\Temp\' FilePath, 'table' As ExportType, 'Sales.txt' OutPutFileName, 'MARINERLAPTOP24\SQL2008' SourceServerName
UNION ALL
Select 'AdventureWorks2008' DB, 'Production.Product' AS TableNameOrQuery, 'C:\Temp\' FilePath, 'table' As ExportType, 'Product.txt' OutPutFileName, 'MARINERLAPTOP24\SQL2008' SourceServerName
UNION ALL
Select 'AdventureWorks2008' DB, '"Select CurrencyCode, Name from AdventureWorks2008.Sales.Currency Where Name <> ''Afghani''"' AS TableNameOrQuery, 'C:\Temp\' FilePath, 'query' As ExportType, 'MyQuery_Currency.txt' OutPutFileName, 'MARINERLAPTOP24\SQL2008' SourceServerName

2. A ForEachLoop container to loop through each of the data dump(s).


3. A Execute process tasks to run BCP utility. You can learn more about BCP in Book on Line

Final thoughts:

• Although you could argue you could build the same process without using SSIS and I still think SSIS works great here.
• While BCP is a SQL Server utility, you could use an equivalent utility/approach when dealing with other database engines. E.g. in Oracle you could use SQLPlus with Spool commands.
• Dataflows are not the only option to move data in SSIS.

* While built-in DataFlow task metadata cannot be altered at run time, with some custom coding, you could generate the packages programatically to achive a similar effect. For more information, see BOL topic

Download the package from here:

Let me know if you have a different approach.

Raf.