November 16, 2011 at 8:42 am
HI ALL
i have been asked to copy 21 sql tables to individual .txt files on a weekly basis (for data warehousing software and thats how the 3rd party wants the data presented)
what do you reckon is the best way to approach such a job?
i was planning on an ssis job, which will have 21 db sources and 21 flat file destinations, but i dont see how to delay source 2 until source 1 is finished and so on, or is it even possible to do so?
does this seem like a realistic way to approach a job like this? or would i be better scripting it to have more control?
cheers
mal
November 16, 2011 at 9:20 am
You should be able to create and SSIS job that utilizes a FOR EACH container so you can loop through the list of SQL tables. Of course, you'll need to create variables here and there to hold the locations so the task knows what to do with them.
You should need much more than the For Each Loop container that has a SQL Datasource control and the File Destination Control, using variables to populate "from which table" to output to "which file and its corresponding destination"
Just a thought...
If you're not strong on SSIS it would porbably be easier doing it via TSQL though...much more coding but less headaches if SSIS isn't your thing.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 16, 2011 at 9:24 am
yes - for got about for each loop containers! could be my answer
also just created a dump out using bcp - which may be easier managed down the line
November 18, 2011 at 4:34 am
i thought bcp commands in sql was going to do the trick - but when i wanted text qualifiers things started to get messy - i was going to need a format file for each table!
back to sis and the drawing board ! 🙁
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply