CODEWITHSUNDEEP
sqlsql-servercsvssis
Martin Brandl
Martin Brandl

Reputation: 58931

Create multiple files based on an output of a custom SSIS component

I have a custom SSIS DataFlow component which has three output columns:

Id
Name
Content

Here is a screenshot of the components output properties: enter image description here

The component returns multiple of these result sets. I am able to pipe the result into a Flat File Destination which gives me something like this:

{E007818F-B9B9-46FF-9231-07367EB96C4F}, nameA.csv, just a text
{9A36EC87-73BC-497D-9592-47FC119CB6E5}, nameB.csv, another text

I need to write each of these result sets into a separate file which name should be the name property and the content should be the content property of the result set. So the desired output in this case are two files:

nameA.csv

just a text

nameB.csv

another text

Unfortunately the result set count is dynamic so I can't just create multiple Flat File Destinations.

Any ideas?

Upvotes: 0

Views: 1367

Answers (1)

MayowaO
MayowaO

Reputation: 370

To split the data into two separate files, you need to use a "Conditional Split" transformation after the source component. In the editor, create a condition for each result set split, for example

  • for split 1:
    Output Name: nameA, Condition: Content == "nameA"
  • for split 2:
    Output Name: nameB, Condition: Content == "nameB"

Configure a Flat file destination for each of the files. Then drag the green connectors from the Conditional Split component to the relevant destination component.

--Updated

Making it dynamic is more complex...

You need to start with an Execute SQL task that returns all names in the Name field; store the result set in an object variable. Then link this to a For-each Loop Container that contains a Data Flow task. Configure the For-each loop to use an ADO Enumerator and select the object variable created above. Also configure a Variable Mapping to store the Name in the current Enumeration (this will be used later for the filename).

In the Data-Flow task, the Source component should be a SELECT statement with WHERE name = 'Name in object variable'. On the destination connection manager properties, configure an Expression for the ConnectionString = variable created in the For-each loop above. This will give each file the name of the current Name in the iteration.

I hope this is helpful.

Upvotes: 1

Related Questions