Programming a loop in a DTS

Question

Programming a loop in a DTS

jlaz

SSC Enthusiast

Points: 165
More actions
March 30, 2005 at 12:40 pm

#87175

Hi all,
I have a DTS that I run weekly where it imports data from a .csv file. There can be more than one of these files with the same structure but different names. Currently, I am executing the DTS for each of these files manually. I am wondering if anyone can provide me with some examples that demonstrate how to incorporate a loop to a DTS that repeats a task. Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

Roughly, you could create a step that checks for the existence of a file in the directory. If a file exists read the first file name into a global variable. Perform the data import on the file and then move the file to another directory (or delete it) on completion of that final task start the first task that checks for the existence of a file... If a file doesn't exist exit.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

philcart SSC-Forever Points: 47796 More actions · Answer 2

There is a good article on this over at http://www.sqldts.com.

--------------------
Colt 45 - the original point and click interface

DCPeterson SSCoach Points: 15266 More actions · Answer 3

Good call, I should have thought of sqldts.com... This article details one method of doing this. It's a bit different than what I suggested, but I didn't provide the code

http://www.sqldts.com/default.aspx?246

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

jiema Mr or Mrs. 500 Points: 516 More actions · Answer 4

Here is another way:

select @cmd = 'COPY ' + @Source_path + @file + ' ' + @Process_path

exec master..xp_cmdshell @cmd

SELECT @Process_path = @Process_path + '\'

-- Clean temp table

DELETE FROM Temp_TABLE

-- Import text file into SQL Temp table

select @cmd = N'BULK INSERT Temp_TABLE' + "'"+ @Process_path + @file +"'"+ ' WITH (ROWTERMINATOR = ''\n'')'

EXEC (@cmd)

--Process data from Temp_TABLE

I have a lot of this kind of processing running everyday against different text format.

Jie Ma

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

We use the loop code in http://www.sqldts.com/default.aspx?246 to process multiple text files in a number of DTS jobs.

You might also want to look at the code at http://www.sqldts.com/default.aspx?218. This provides information on how to branch around a section of code. Handy for that pesky time when you run your jobs and there are no files!!