DTS problem

Question

Post reply

DTS problem

pinky

SSCommitted

Points: 1997
More actions
April 13, 2005 at 10:48 am

#61856

Hi i have a problem in DTS. I want to import multiple files which have same columns( dynamic i.e., total number of files always change from day to day) into a single table in sql server. Can anyone help me to generate a script for this problem

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

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

Seth Washeck Mr or Mrs. 500 Points: 578 More actions · Answer 1

File Watcher:

http://www.databasejournal.com/features/mssql/article.php/3319261

then union all

Kindest Regards,

Seth Washeck

If nothing in this world satisfies me, perhaps it is because I was made for another world.
C. S. Lewis

mwagner SSC Veteran Points: 204 More actions · Answer 2

what kind of files are they? i do something similiar with text files. i wait for them to be ftp'd from the mainframe and once they are done run the following command:

exec

master..xp_cmdshell 'COPY \\path\*.txt \\path\All.txt'

then i load in the all.txt file that has all files in it.

pinky SSCommitted Points: 1997 More actions · Answer 3

They are the text files and the files are dynamic i.e number of files change daya by day.

Bill Nye Hall of Fame Points: 3248 More actions · Answer 4

For another approach:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=112360#bm113285

DAVNovak Hall of Fame Points: 3120 More actions · Answer 5

A slightly different approach to handling all of the file manipulation in an Active X Script in the DTS would be to handle it in the stored procedure. You can set a nice simple loop in the SP to handle all the files. This way it does not matter if there are three files or thirty files. You can also check the file size before the DTS loads it in case it is empty. You will need permission to run the 'xp_CmdShell' SP from the Master DB.

This is set up for Windows 2003 Server and SQL Server 2000.

The first step is to find out how many files you have:

DECLARE @vcText varchar(300),

@vcFile varchar(80),

@dFileSize decimal(28,0)

CREATE TABLE #TmpDirectory (vcLine varchar(120))

SET @vcText = 'DIR ' + {Full path to files using UNC nomenclature}

INSERT INTO #TmpDirectory

EXEC Master..xp_CmdShell @vcText

DELETE FROM #TmpDirectory

WHERE vcLine IS NULL

OR vcLine LIKE '%.Txt' --replace 'Txt' with what ever file extension you are looking for.

--2nd Step get file name and size to start the loop process.

SELECT TOP 1 @vcFile = RTRIM(SUBSTRING(vcLine, 40, 30)),

@dFileSize = CONVERT(decimal(28,0), REPLACE(SUBSTRING(vcLine, 21, 18), ',', ''))

WHILE @@ROWCOUNT <> 0 --loop

BEGIN

IF @dFileSize > 0

BEGIN --Rename file to the name used in the DTS package for loading.

SET @vcText = 'RENAME {UNC Path}\' + @vcFile + ' Staging.Txt'

EXEC xp_CmdShell @vcText

--Call the DTS Package loading the file

SET @vcText = 'DTSRUN /S ' + @@SERVERNAME + ' /E /N {DTS Name}'

EXEC Master..xp_CmdShell @vcText

--Move the file to an archive subfolder

SET @vcText = 'MOVE {UNC Path}\Staging.Txt {UNC Path}\Archive\' + @vcFile

EXEC Master..xp_CmdShell @vcText

END

ELSE --Move the empty file to the archive subfolder

SET @vcText = 'MOVE {UNC Path}\' + @vcFile + ' {UNC Path}\Archive\' + @vcFile

EXEC Master..xp_CmdShell @vcText

END

--Get next file to load in the loop.

SELECT TOP 1 @vcFile = RTRIM(SUBSTRING(vcLine, 40, 30)),

@dFileSize = CONVERT(decimal(28,0), REPLACE(SUBSTRING(vcLine, 21, 18), ',', ''))

END

This process will handle 0 to as many file as you have space for on the harddrive. Good luck.

Dave Novak