3

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 enter image description here

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

Kallie Reyneke
  • 57
  • 5
  • 2
    What is the condition you are trying to split with? – Yahfoufi Apr 19 '17 at 12:07
  • 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 Apr 19 '17 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 Apr 19 '17 at 14:31

1 Answers1

2

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

enter image description here

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I suspect a quick glance through my [previous answers](http://stackoverflow.com/search?q=user%3A181965) 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](http://stackoverflow.com/search?q=user%3A181965+shred) [FELC](http://stackoverflow.com/search?q=user%3A181965+FELC) etc – billinkc Apr 19 '17 at 19:06