December 26, 2003 at 6:15 am
How can I export form several tables to the same text file using DTS. I will be using sql select statements. The columns selected from each table will be different.
The text file will be renamed for each run using the time stamp.
I have done this using osql but wants to improve on it by using the DTS.
Any ideas. Thanks for your help.
December 26, 2003 at 7:55 am
You can export to different text files and then run a cmd task to execute a copy of all your text files to a single file.
December 26, 2003 at 9:50 am
You could also use UNION and combine all the separate selects into one statement.
December 26, 2003 at 12:04 pm
Setup a connection object from the sql server and one text file destination connection for each text file you want to export to. You can then hightlight the server connection, ctrl select one of the text file destinations, right click and select transform data task
Right click on the silver direction arrow, properties. Within the properties dialog you should be able to paste in any SQL statement you want. Then use the copy columns task under transformations tab to specify what columns to copy.
Be sure to delete the default transformations and setup just one transform task using copy columns. It's more efficient.
December 26, 2003 at 3:39 pm
data czar: The UNION has been tried before but did not work.
cliffb: Your idea is interesting and will be tried. I have done something on this line before but did not use different text files.
So what do you suggest for combining the various text files together.
Thanks.
December 26, 2003 at 6:57 pm
Another approach you might try is to save the results of each of your sql statements to global variables. (under execute sql task properties, create a global variable in the output parameters tab, select the rowset option button, and select the variable you just created from the drop down menu.) You can then open the global variable rowset that the query was saved to using vbscript in an ActiveX task, and write the results to a text file using the file system object. If you save all of your sql statements to global variables, you could open them all in the same ActiveX task and write them all to the same text file.
Good luck!
December 29, 2003 at 7:28 am
You can also delegate task of creating a combined text file to a process task, which can
execute a batch file.
1. Set up a db connection;
2. Create multiple text file connections, i.e. \\netserver\destdir\FILE1.txt, \\netserver\destdir\FILE2.txt, and \\netserver\destdir\FILE3.txt;
3. Create transformation task for each text file with SQL statements;
4. Create a process task with Win32process property pointing to your batch file, i.e. \\netserver\destdir\COMBINED*.txt.
Connect your text file connections with the processs task by Success workflow.
This is a sample of a batch file that will combine, in this case, three files together and will append a time stamp to the file name:
-----------------------------------------------------------------------------------
REM *******************************************************************************
REM ** Sets variable for the file renaming portion of the job **
REM ** A FOR..IN..DO loop is used to take two "tokens" from the date, **
REM ** displayed in the DAY MM/DD/YYYY format with the /t switch. The loop **
REM ** assigns it to variable %TEMPDAY%. **
REM ** %MM% extracts 2 characters starting at 'date /t' output character 0 **
REM ** %DD% extracts 2 characters starting at 'date /t' output character 3 **
REM ** %YYYY% extracts 4 characters starting at 'date /t' output character 6 **
REM *******************************************************************************
for /F "tokens=2" %%F in ('date /t') do set TEMPDAY=%%F
set MM=%TEMPDAY:~0,2%
set DD=%TEMPDAY:~3,2%
set YYYY=%TEMPDAY:~6,4%
@echo off
pushd \\netserver\destdir
if exist COMBINED*.txt del COMBINED*.txt
type FILE1.TXT > COMBINED%yyyy%%mm%%dd%.txt
type FILE2.TXT >> COMBINED%yyyy%%mm%%dd%.txt
type FILE3.TXT >> COMBINED%yyyy%%mm%%dd%.txt
del FILE1.TXT
del FILE2.txt
del FILE3.TXT
popd
-----------------------------------------------------------------------------------
Bets of luck.
December 29, 2003 at 7:39 am
I would Follow "Racosta" Suggestion!
Create Separated Text files and all you will have to run is:
Copy /b *.txt AllCombined.asc
* Noel
December 29, 2003 at 7:41 am
Correction: in step 4 point Win32process to your batch file, not to the final combined file, i.e. \\netserver\destdir\COMBINEFILES.bat
Sorry about that.
December 29, 2003 at 9:41 am
Thanks all. rascota's suggestion seems short and gets the job done.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply