August 5, 2008 at 8:12 am
Ok, I have a semi-complicated situation where I have a basic query that needs to output into several text files. My datasource is a DB2 OLE connection. I have about 10 databases in DB2, and I need to pump the data from 10 different sources into 10 different text files.
1) For 1 of the text files, it needs data combined from 2 DB2 databases. Each database has it's own connection. How can I do this ?
2) The DB2 databases hold basically the same information but for different departments of the company. The query is almost exactly the same aside from a couple of "custom" columns that I create with a "SQL command from a variable". When I pull data from different sources, the custom column needs to change so I can't seem to be able to use the same SQL variable. I don't want to duplicate all this code for a few columns. Any ideas are appreciated.
August 5, 2008 at 8:35 am
Example for my question:
Select f1, f2, f3,......, c1 = 'ART'
from table1.......
where .........
to text file 1
Select f1, f2, f3,......, c1 = 'MUSIC'
from table1.......
where ........
to text file 2
There are many lines in the select statement so I'd rather not duplicate them in different variables. The number of columns are the same.
thx
August 5, 2008 at 8:48 am
if the metadata for each source is the same, you could populate an ado object with the various department names along with the source connection string and the destination file path. then, loop through the ado recordset, assigning these three values to three variables.
then, within the loop, you have a data flow task which reads from the destination connection (which has the connection string pointing to the connection string variable), you use a derived column to add in the department name and send the whole lot to the destination file.
does this help?
tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply