using database lookup with DTS import

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

using database lookup with DTS import

Author

Topic

dcnice
Starting Member

2 Posts

Posted - 2003-10-28 : 18:44:58

I would like to be able to import multiple text files on a daily basis into SQL Server 2000 using a DTS package.
Using the DTS wizard I can create a package which will import a fixed filename into the SQL database (it works as required). By scheduling it I can make it run daily (stating the obvious I know!).
I don't know if this is possible, but rather than have a hardcoded filename to import, I would like to take the import filename from a table which resides in my SQL server. I would then require it to loop through each record and import the relevant file.

Is this possible? If so any help/pointers would be much appreciated as I have not yet managed to familiarise myself with DTS (everyone has to start somewhere I s'pose!).

Thanks in advance

Dave

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-28 : 18:50:08

One option is using DTSGlobalVariables in an ActiveX Script
Another option is to look into bcp/bulk insert.

Related reading - http://www.nigelrivett.net/ImportTextFiles.html

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 18:56:30

Another option is using VBScript to do this. The VBScript code would copy the newest file in a directory into another directory. It would then rename the copied file in the "another directory" to what the DTS package expects.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-28 : 19:02:52

ehorn, how would bcp help you import data from a dynamically named file?

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-28 : 19:19:16

quote:
Originally posted by tduggan

ehorn, how would bcp help you import data from a dynamically named file?

Tara

ALTER PROC dbo.BCPImport
@chrPath varchar(255),
@chrFile varchar(255),
@chrUser varchar(30),
@chrPassword varchar(30),
@dbName varchar(100),
@table varchar(100)
AS
SET NOCOUNT ON

DECLARE @chrCommand varchar(1000)
SELECT @chrCommand = 'bcp ' + @dbname + '..' + @table + ' in ' + @chrPath + + @chrFile + ' /c /t, /r\n /S' + @@SERVERNAME + ' /U' + @chrUser + ' /P' + @chrPassword
select @chrcommand
--EXEC master..xp_cmdshell @chrCommand
go

exec dbo.BCPImport '\\somepath\', 'somefile.txt','ehorn','password','yourdb','yourtable'
go

DROP PROC dbo.BCPImport
go
--Related reading - http://www.sqlteam.com/item.asp?ItemID=3207

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-29 : 11:56:35

Yeah, but you would need to pass the dynamic file name into the stored procedure. What happens if you don't know what the file name is going to be? How would you know what to pass into the stored procedure? With VBScript, you could easily do this.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-29 : 13:06:02

quote:
Originally posted by tduggan

Yeah, but you would need to pass the dynamic file name into the stored procedure. What happens if you don't know what the file name is going to be? How would you know what to pass into the stored procedure? With VBScript, you could easily do this.

Tara

set nocount on
declare @chrcommand varchar(100)
--Get a list of input files
SELECT @chrcommand = 'dir ' + 'c:\temp\' + '*.txt'
if exists (select * from tempdb.dbo.sysobjects where name like '#DirResults%')
DROP TABLE #DirResults
if exists (select * from tempdb.dbo.sysobjects where name like '#files%')
DROP TABLE #files

CREATE TABLE #DirResults (Diroutput VARCHAR(255))

INSERT #DirResults EXEC master.dbo.xp_cmdshell @chrcommand
SELECT cast(replace(replace(replace(left(diroutput,16), ' ', ' '),'a','AM'),'p','PM')as datetime) as filedate,rtrim(substring(diroutput,40,215)) AS filename
INTO #files
FROM #dirresults
WHERE isnumeric(left(diroutput,1)) =1
And right(diroutput,1) <> '.'
ORDER BY substring(diroutput,40,215)

select * from #files

drop table #dirresults
drop table #files

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-29 : 13:09:11

But that would get all of the files in a directory. Typically when you receive a file from an outside source, the file goes into a directory where the other files are stored. But you only want to import the data from the newest file since you've already imported the data from the other files. I guess you could still use your scripts, but you would have to delete the files from the directory after you are done so that you don't keep importing the data. In my environment, we do not want to delete the files, so we only process the newest file.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-29 : 21:24:09

1. Keep each imported file's name in a permanent table. Delete those files from the temp table before beginning the import process.
2. On the assumption that you'll only want to import the most recent files, delete all files not matching the most recent file date before you begin the import process.

Either method allows all files to stay in the directory forever. I would suggest the first option as the best one, since you can program it to ensure the file was properly imported before keeping a permanent record.

Here's a tweak to ehorn's code that makes the import easier:

set nocount on
declare @chrcommand varchar(100)
--Get a list of input files
SELECT @chrcommand = 'for %a in (' + 'c:\temp\' + '*.txt) do @echo %~na%~xa;%~tam >files.txt'
EXEC master.dbo.xp_cmdshell @chrcommand

if exists (select * from tempdb.dbo.sysobjects where name like '#files%')
DROP TABLE #files

CREATE TABLE #files(filename varchar(128) not null, filedate datetime not null)
BULK INSERT #files FROM 'files.txt' WITH (FIELDTERMINATOR=';')

select * from #files
drop table #files

For more information on the DOS for command, see the Windows help file. It has many extremely handy features that let you do some incredible stuff. Also check this blog entry:

http://weblogs.sqlteam.com/robv/posts/181.aspx

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-29 : 21:34:58

Very cool command line trick robvolk!! Thanks for the tip and link

dcnice
Starting Member

2 Posts

Posted - 2003-10-30 : 06:21:05

I would like to thank you all for your help.
I have it up and running now.
I used a combination of scripts from the following 2 sites
http://www.databasejournal.com/features/mssql/article.php/1462571
http://www.sqldts.com/default.aspx?298

Whilst it does not check for file existance, this is not a worry for me.

If anyone needs a copy of the DTS package please feel free to e-mail me. dcnice@yahoo.com
Thanks again.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources