January 13, 2011 at 1:30 pm
I need to export large data sets into multiple files based on the number of records... data will be csv and will be exported to .txt file. Each text file should have less than 65K records so that it can be loaded into excel. Can I get this done using SSIS? Or someone has some Sql script that can be used for breaking this export into multiple files?
January 13, 2011 at 4:32 pm
May not be the best way to do it, but you could create a stored procedure that accepts a starting ID and a number of records as input parameters and has the ending ID as an output parameter
MyExportProc @StartID, @NumRecords, @EndID OUT
Then what you can do is loop on this with an xp_Cmdshell call to bcp using dynamic SQL and just keep passing the appropriate IDs (@EndID will become the @StartID of the next call). If you have an @EndID of say -1 you exit out of the loop. You'd need to create a time stamped filename or something for the bcp output too.
In order to get the right number of records in the proc you could do a select top @NumRecords where the ID is greater than your @StartID (if you have 2008 use a variable, otherwise you'd have to do dynamic sql). You will also have to store the max ID in the @EndID variable.
If you need more help with this let me know. And if someone has a better way to do this let US know 🙂
January 13, 2011 at 9:36 pm
Thanks Johnny that is what I am working on, but got another way to do it in SSIS as I was using SSIS for steps before this.... create a column with row number and use rank() to give ranking like for first 65K rank 1 and so on... and then use conditional split in ssis using the rank...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply