How to read a file name and keep it in DTS package as source file 1

[mariayx]

Hi everyone,
I need to read file names in a directory on an FTP server one at a time, pass the file name as a source data file in a DTS package, then load it into SQL Server tables. Keep on doing that until all the files in the directory are read and processed.
My question lies with the file name reading and passing it as a parameter part. Has any guru out there done something similar that can help?
Thanks a lot for any help!
mariayx

 

[JayKusch]

Have done this type of step many times!!! Here is how I have achieved "my" desired result ...

First, create a table, temp if it is a one time job ... a real table if this is a daily job. Somewhat like this (for a static table) ... We are also creating an incrementing value to use in our looping structure later. it is called "FOD_Key". With each filename we add to the table, we will increment this field by 1.

CREATE TABLE [dbo].[Files_On_Disc] (
[FOD_Key] [int] IDENTITY (1, 1) NOT NULL ,
[Files_On_Disc_Text] [varchar] (2000) NULL
) ON [PRIMARY]

If a real table is used, you may want to trunc the table on each new run, so ...

TRUNCATE TABLE Files_On_Disc

Now we can load your table with all the files needed ... First we create a text file, LoadFiles.txt, that contains all of your desired filenames.

EXEC Master.dbo.xp_cmdshell
'dir C:\MyDir\MyFiles.* /s > C:\LoadFiles.txt'

Then we read your file into our new table ...

INSERT INTO Files_On_Disc
EXEC Master.dbo.xp_cmdshell 'type C:\LoadFiles.txt'

Now we clean/delete our working text file

EXEC Master.dbo.xp_cmdshell 'del C:\LoadFiles.txt'


Now we are ready to process each filename ... So we ...

declare @RecordMax Int
declare @RecordCnt Int
declare @ProcessThisFile VarChar(200)

SET @ProcessThisFile = ''
SET @RecordCnt = 1
SET @RecordMax = (SELECT MAX(FOD_KEY) FROM Files_On_Disc)

WHILE @RecordCnt <= @RecordMax
BEGIN
SELECT @ProcessThisFile = Files_On_Disc_Text
WHERE FOD_Key = @RecordCnt

-- DO THE REST OF YOUR PROCESSING HERE !!!

SET @RecordCnt = @RecordCnt + 1
END


Hope this get you started on the right track!

Thanks

J. Kusch

 

[mariayx]

Thanks a lot! That was very helpful! I will give it try. In my case, I will need to read a directory from a remote FTP server though.
mariayx

 

[mariayx]

Hi Jay,
Here is what I did before I ran into a problem.
I mapped the FTP directory to my local drive. And ran the following commands in DTS(I was logged on to the remote SQL Server at that moment from my PC and ran the step from there), but LoadFiles.txt did not get created on my local machine. I got one record inserted in the Files_On_Disc table with empty file name in it.
EXEC Master.dbo.xp_cmdshell
'dir G:\MyDir\MyFiles.* /s > C:\LoadFiles.txt'
INSERT INTO Files_On_Disc
EXEC Master.dbo.xp_cmdshell 'type C:\LoadFiles.txt'

But if I ran the 'dir G:\MyDir\MyFiles.* /s > C:\LoadFiles.txt' from DOS alone, the LoadFiles.txt got created. Then I ran INSERT INTO Files_On_Disc
EXEC Master.dbo.xp_cmdshell 'type C:\LoadFiles.txt', still no file names inserted.

I tried the same thing with the mapping drive on the db server. Got the same result. Since I don’t have access to DB server machine. I could not see whether LoadFiles was created.
Do you have an idea what is going wrong?

Thanks!
mariayx