CODEWITHSUNDEEP
sql-serverssis
Lock
Lock

Reputation: 5522

SSIS- Split output into multiple files

I am using SSIS Data Tools to create data extracts from a legacy system. Our new system needs the files that it imports to be split into 5MB files.

Is there anyway that I can split the files into separate files? I'm thinking that because the data is already in the database, I can do a loop, or something similar, will select a certain amount of records at a time.

Any input appreciated!

Upvotes: 2

Views: 4169

Answers (1)

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3964

If your source is SQL, use the Row_Number function against the table key to allocate a number per row e.g.

Row_number() OVER (Order by Customer_Id) as RowNumber

and then wrap your query in a CTE or make it a sub query with a where clause to give you the number of rows that will equate to a 5MD file e.g.

WHERE RowNumber >= 5000 and RowNumber <10000

You will need to call this source target several times (with different Row Start and Row End values), so probably best to

  • Find number of total records in control flow and set a TotalRows parameter
  • Create a loop in your control flow

  • Set 3 parameters in your control flow to iterate the through each set of records and store the data in seperate files. e.g. first loop would set

    RowStart = 0

    RowEnd = 5000

    FileName = MyFile_[date]_0_to_4999

Upvotes: 4

Related Questions