bensonlaw
asked on
DTS Export Multiple CSV file (HELP)
ok i know how to use DTS to export one sql query into a csv file
but the problem now is
i need to select multiple record out &export it all to diff csv file
i cant keep create alot of DTS object to extract diff diff file
cuz it would be alot n dynamic
for example
select * from student where class="6M"
i need to
select all student from diff class
and save into diff csv file
i did some reserach
and come through BCP
and stored procedure to export to csv
but yet not really understand it much
anyone can help on this
what the best implmentation
if possible show some sample code
thanks alot
but the problem now is
i need to select multiple record out &export it all to diff csv file
i cant keep create alot of DTS object to extract diff diff file
cuz it would be alot n dynamic
for example
select * from student where class="6M"
i need to
select all student from diff class
and save into diff csv file
i did some reserach
and come through BCP
and stored procedure to export to csv
but yet not really understand it much
anyone can help on this
what the best implmentation
if possible show some sample code
thanks alot
You can set up 2 connections in DTS for this. The first will be your database, the second a destination text file (which you should create first). Then you can use a transform data task to copy the data you require into the text file.
Let me re-state that in a clearer way.
Create your csv file (e.g. diff.csv).
In your DTS package, create a connection to your database, and a connection to the csv file (using data source "Text File (Destination)").
Then select both connections and create a new "Transform Data Task" between them.
Create your csv file (e.g. diff.csv).
In your DTS package, create a connection to your database, and a connection to the csv file (using data source "Text File (Destination)").
Then select both connections and create a new "Transform Data Task" between them.
ASKER
this method i know
let say i wan do a multiple query
select student from class a
select student from class b
and the list goes on
i wan to run a loop in it
so i can export the query into diff diff csv file
so the file will be
classa.csv
classb.csv
classc.csv
let say i wan do a multiple query
select student from class a
select student from class b
and the list goes on
i wan to run a loop in it
so i can export the query into diff diff csv file
so the file will be
classa.csv
classb.csv
classc.csv
Ah I see, sorry. That's more tricky, I'll give it some thought!
u can create more connections between the database and different csv files in the same DTS package ...
ASKER
ya i know
but the class is like 50 class
u wont wan create 50 connection to grab all
and the class is dynamic
but the class is like 50 class
u wont wan create 50 connection to grab all
and the class is dynamic
create one connection to database and another to one of ur csv file .. then create a "activex script task" which will destination connection based on ur requirement ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I told you it was more tricky ;)
That is an Understatement... LOL ...
Took me a goood long time to figure out how to do this ..
I replaced a DTS package that had 50 steps with one that had 10
and i didn't have to update it every time someone added ANNOTHER
file to process .. JUST loop .. and i put all my steps into config table(s) ..
but thats ANNOTHER story ..
Jay
Took me a goood long time to figure out how to do this ..
I replaced a DTS package that had 50 steps with one that had 10
and i didn't have to update it every time someone added ANNOTHER
file to process .. JUST loop .. and i put all my steps into config table(s) ..
but thats ANNOTHER story ..
Jay
Thank Darth