November 30, 2009 at 12:43 am
Hi
I have a requirement to write 100 rows in flat file and then change the flat file connection string and then again write rows from 101 to 200.. and so on until i write all the records.
I'm trying to do this in SSIS . I know how I can pass dynamic string connection by using expression and varaible.
But somehow stuck to increemet this 100 and write in second file. I think for loop should be used but not able to understand how should I use it.
may be some quick ideas lite up my mind
November 30, 2009 at 10:36 am
Search for a paging script. That's the idea of what you're trying to do. I'm not sure how to do it in SSIS, but that's the idea.
December 2, 2009 at 2:58 pm
Off the top of my head, if you have a sequential ID you could setup a loop using something like a CurrentID and select the "TOP N" rows where the ID is greater than than the CurrentID. The loop would then increment CurrentID, change the file name and keep going.
It would make multiple calls to the dp though. and you would have to stop the loop.
Another idea would be to build an SP or query that "paged" the data in the result set and the loop would be based on the number of "pages", so when it changes the filename / connection would change.
Just some ideas.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
December 2, 2009 at 4:12 pm
The suggestion that Curtis gave about the CurrentID and Top N rows is essentially the same sort of configuration we have done. It works surprisingly fast for what it needs to do.
You would have the LoopID as well as a RowCountID in your loop.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply