SSIS: How to load a data from a table to a file, into 50 000 batches (50 000 rows at a time)

Question

SSIS: How to load a data from a table to a file, into 50 000 batches (50 000 rows at a time)

sirkinghorse

SSC-Addicted

Points: 457
More actions
June 25, 2018 at 5:20 am

#363729

Hi,
I have a table with 200 000 records, I need to send a file with 50 000 records. I'm using MS SQL 2016. I'm not sure how to go about it but I think my logic will be on my SSIS Package. help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

To confirm, are you saying you therefore need to create 4 files (as there 200,000 rows)? I assume this needs to scale out as well?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sirkinghorse SSC-Addicted Points: 457 More actions · Answer 2

I need to produce a file with 50k records/batch for whatever number of records in the table, the table currently it's at 200k, it could have been 130k, then I would have sent 3 files, 50,50 then 30k.

Thom A SSC Guru Points: 99133 More actions · Answer 3

This is a simp,e description opf the steps you need to do; as I don't have a lot of detail to go on.

OK, I would firstly create a Execute SQL Statement task, and return the number of rows to a variable. In your package as well, you'll also need for few more further variables for the FileName, Offset and Fetch values, and another for your dynamic SQL.

The SQL variable will need to be something like:
="SELECT YourColumns FROM YourTable WHERE YourWhereCriteria ORDER BY YourIDColumn OFFSET " + (DT_WSTR,10) user::Offset + " ROWS FETCH NEXT " + (DT_WSTR,10) user::Fetch + " ROWS ONLY;" (this is UNTESTED code)

Create a For Loop container, and set it to loop while the value of Off Set is less than the value of your total rows variable. In there, create your dataflow, and use the value of your dynamic SQL as the source. Then export that data to your file (which'll have a dynamic name).

Then, after the dataflow, use a Expression task to increase the value of your Offset variable by your Fetch Variable's value, and then another to update the name of your filename (or have the filename as an expression, derived off the valyue of OFFSET). Your container will then loop until all the rows are processed, generating a new file each time.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 4

sirkinghorse - Monday, June 25, 2018 5:20 AM
Hi,
I have a table with 200 000 records, I need to send a file with 50 000 records. I'm using MS SQL 2016. I'm not sure how to go about it but I think my logic will be on my SSIS Package. help.

Why would you need to limit the file to only 50,000 rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sirkinghorse SSC-Addicted Points: 457 More actions · Answer 5

Batches of 50k, that's the requirements. if I have 200k in the table, they want that in a file, but 50k per file.

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 6

sirkinghorse - Monday, June 25, 2018 6:32 AM
Batches of 50k, that's the requirements. if I have 200k in the table, they want that in a file, but 50k per file.

Ah... got it. Thank you for the feedback. I hate such artificial limits.

Did Thom's suggestion do it for you or do you still need help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

T3st0 Old Hand Points: 394 More actions · Answer 7

I've done something similar a few months ago ... however I just used a BCP out command to generate the files inside SSIS.

The overall flow was something like:

Figure out how many rows to export and you divide that by 50k. This tells you how many times you need to loop through.
Start looping through.
In the BCP out command you can specify to export 1 - 50k rows in the first loop through, 50,0001 - 100k etc. (don't forget to order by in the query, otherwise you will export duplicate rows potentially).
Increment loop and keep going until everything is exported.

Hope that helps.