April 13, 2005 at 10:48 am
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
April 13, 2005 at 1:27 pm
April 14, 2005 at 7:10 am
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.
April 14, 2005 at 7:20 am
They are the text files and the files are dynamic i.e number of files change daya by day.
April 14, 2005 at 4:54 pm
April 15, 2005 at 12:33 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply