Solved

DTS Export Multiple CSV file (HELP)

Posted on 2004-08-18
13
953 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:bensonlaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

by:alexpreston
ID: 11828267
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.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11828287
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.
0
 

Author Comment

by:bensonlaw
ID: 11828324
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



0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 3

Expert Comment

by:alexpreston
ID: 11828362
Ah I see, sorry. That's more tricky, I'll give it some thought!
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 11828938
u can create more connections between the database and different csv files in the same DTS package ...
0
 

Author Comment

by:bensonlaw
ID: 11828993
ya i know
but the class is like 50 class
u wont wan create 50 connection to grab all
and the class is dynamic
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 11829019
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 ...
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 50 total points
ID: 11829925
LOL..

Not even close guys..

This is what you will need to do ...
you will want to repetitively invoke your csv export step with an active-x Task
effectively taking over flow control from dts
the active-x script task will be first followed by a SETUP loop step
the export to csv step
then the Flow control Step active-x Script
which will reset the status of the Export step and
increment the loop counter.

You probably should setup a query from and load it into a global variable
with an output parameter in a SQL task step. (ALSO a pain to do)
and do the Next record logic in the Flow control active-x script

and you will have to dynamically configure the csv export step for each
execution as well
You will have to get the name of the task by going into disconnected edit from the main menu and looking through the steps although the name will be DTSTask_DTSDataPumpTask_1 Unless you have created other data pump tasks in this
package.

Like so

function main()
set pkg=dtsglobalvariables.parent
pkg.steps("DTSTask_DTSDataPumpTask_1").executionstatus=DTSSTEPExecStat_Waiting
pkg.steps("DTSTask_DTSDataPumpTask_1").DestinationObjectName="c:\thenewname.csv"
pkg.steps("DTSTask_DTSDataPumpTask_1").SourceSQLStatement="select * from sysusers where [name]='fred' "

end function
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11829971
I told you it was more tricky ;)
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11830321
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

0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12142748
Thank Darth
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question