SSIS Condition split based on column value

Question

Good Day All,

I have a select query where i pull the data from sql ie

select invno , date_received from sales

What i want to do is split the file into multiple files using conditional split. I don't know how to set the condition i tried as below

But it just creates one file, how do i create the multiple files based on column value if i don't know what the column value would be?

Also i would like to assign the column value ie INVNO to filename as well to prevent overwriting of files

Given your supplied source query, for each invno you would like to create an output file with the "other data" date_received etc, yes? — billinkc
– billinkc, Commented Apr 19, 2017 at 13:35
Yes that is not complete query just sample so invno 1234 creates a file , invno 1235 creates seperate file etc, i just don't know how many different invno there will be — Kallie Reyneke
– Kallie Reyneke, Commented Apr 19, 2017 at 14:31

billinkc · Accepted Answer · 2017-04-19 19:05:55Z

2

Using the out of of the box componentry, your best bet would be to have something like this

The Execute SQL Task will return a Full Result Set to an SSIS variable of type Object. Your query would generate the distinct set of INVNO. SELECT DISTINCT T.INVNO FROM dbo.Sales AS T;

The Foreach Loop Container is then going to "shred" that recordset into a single instance of our INVNO. This requires you to have a variable, probably of type String, to receive that number.

The Data Flow Task will have as the source query a parameterized query. Assume OLE DB Connection manager, that'd be select INVNO, date_received FROM dbo.Sales AS S WHERE S.INVNO = ?; and then you map in the current value of INVNO (assigned as part of the shredding from the FELC)

Finally, the Flat File Connection Manager will have an Expression on the ConnectionString property that factors in the full path to the output file. It could be something as simple as "C:\ssisdata\" + @[User::Invno] + ".csv"

Oh, and set DelayValidation = True for the Flat File Connection Manager and the Data Flow Task.

answered Apr 19, 2017 at 19:05

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

billinkc Over a year ago

I suspect a quick glance through my previous answers will turn up many results for pieces of this answer but I couldn't think of an exact duplicate to hammer it closed. i.e. shred FELC etc

Collectives™ on Stack Overflow

SSIS Condition split based on column value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related