Recently I had an interesting problem in Microsoft SQL Server Integration Services (SSIS). I had to append data to a raw file in a for each loop. A raw file destination component can be configured to have the following behavior: Append, Create always, Create once, Truncate and append. None of these options will let you truncate the file the first time the loop runs and after it only append to the file. Unfortunately SSIS has no task to truncate a raw file without writing to it. But there is a work around! I found it after spending some time doing Google searches. Because the work around is not so well know or easy to find I decided to blog about it.
How to do it (short)?
Create a Data Flow Task (DFT) before the For Each Loop Container. In the DFT create either a flat file source or database source with the same metadata as the raw file has to get. Connect the source to a Raw File Destination that writes to the raw file and set it on ‘Create always’. Now the Raw File will always be empty before the For Each Loop and the For Each Loop can append to it.
How to do it (long)?
For example I want to load data from all sales tables in the AdventureWorks database into a raw file using a for each loop.
1. Create a database with two tables were you will prepare your metadata.
2. Populate the SalesTables table with tables which you want to query in the for each loop. Keep the RawFileSalesMeta table empty, we only want to prepare the right metadata here (column names and datatype).
3. Load the content in the TableNames table into a variable in SSIS. To do this you need to create an variable of the type Object and query to data into it with an Execute SQL Task.
Create the SSIS variable:
Write the following query:
SELECT TableName FROM SalesTables
Set the result set of the Execute SQL Task on Full result set. And set up the right Result Set – Variable mapping.
4. Create a DFT, we will prepare an empty raw file in this step.
Add an OLE DB Source component with the following query (I use TOP (0) to be sure no actual data gets loaded, I just want the metadata here):
SELECT TOP(0) [ProductKey]
,[OrderDateKey]
,[DueDateKey]
,[ShipDateKey]
,[PromotionKey]
,[CurrencyKey]
,[SalesTerritoryKey]
,[SalesOrderNumber]
,[SalesOrderLineNumber]
,[RevisionNumber]
,[OrderQuantity]
,[UnitPrice]
,[ExtendedAmount]
,[UnitPriceDiscountPct]
,[DiscountAmount]
,[ProductStandardCost]
,[TotalProductCost]
,[SalesAmount]
,[TaxAmt]
,[Freight]
,[CarrierTrackingNumber]
,[CustomerPONumber]
FROM [AdventureWorksMetadata].[dbo].[RawFileSalesMeta]
Add a Raw File Destination component and set the Write Option on Create Always. Go to the columns tab and make sure all columns are checked.
Execute this DFT once to create the Raw File now. We will need it’s metadata definition later on.
5. Set up the For Each Loop container and create a dynamic query.
Create a SSIS variable called Table.
Add a for each loop container to your control flow and set up the Collection tab like this:
Set up the Variable Mappings to write a table name to the variable each time the loop enumerates.
Create a SSIS variable of the data type string. This variable which will hold the query. Add the following expression to the variable:
“SELECT [ProductKey]
,[OrderDateKey]
,[DueDateKey]
,[ShipDateKey]
,[PromotionKey]
,[CurrencyKey]
,[SalesTerritoryKey]
,[SalesOrderNumber]
,[SalesOrderLineNumber]
,[RevisionNumber]
,[OrderQuantity]
,[UnitPrice]
,[ExtendedAmount]
,[UnitPriceDiscountPct]
,[DiscountAmount]
,[ProductStandardCost]
,[TotalProductCost]
,[SalesAmount]
,[TaxAmt]
,[Freight]
,[CarrierTrackingNumber]
,[CustomerPONumber]
FROM [AdventureWorksDW2008R2].[dbo].” + @[User::Table]
Assign the value ‘FactInternetSales’ to the variable Table, so we can validate the external metadata with a real query (that is prepared in the query variable).
Now add a DFT to the For Each Loop container
Add an OLE DB Source component in the new DFT and configure it so it gets the query from the variable.
Add a Raw File Destination to the DFT and configure it to append.
Now everything is set. The package will query each table you add to the SalesTables table (though the metadata has to be the same).
rolandholtermann 