How to create a separate excel file on dataflow task in ssis?

Question

I am new to SSIS. I am trying to create a separate excel file dynamically in data flow task for each iteration of the for-each loop? Please guide

Answer 1

You can utilize the following approach.

Create an excel file template on the folder where you want to drop the new files.

Connect your excel file destination to the template file created in the folder.

Create two variables:

1. variable: IterationCount Data Type Int default value 1.

2. Variable: FileName Data Type: string
   Expression = "Mybasefilename_" + (DT_STR, 4,1252)[User::IterationCount] + ".xlsx"

On your excel file connection hit right click and hit properties go to expression and hit three ellipses and look for filename property. Set the property value choosing @[User::Filename] variable. If the Name property is not available use the connection string property, however, you should add the folder path as part of your filename variable to create the entire file destination and name.

Last step in your FELC you need to update the IterationCount variable in each iteration. So, we cannot catch the index of the iteration then you need to use an expression in the FELC, expression task, or a script task to update the IterationCount variable. Expression task example: @[User::IterationCount] = @[User::IterationCount] + 1

Helpful Links:

Microsoft - SSIS ForEach Loop Container

SSIS Expression Task

SSIS - Updating variables using Script Task