DTS Import help - SQL Server Forums

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)

DTS Import help

Author

Topic

boogybaz
Starting Member

8 Posts

Posted - 2007-03-15 : 04:19:14

I am trying to import a number of text files into a table within SQL server. They are all the same format, but obviously with different filenames. What I need to do, is import every file within the directory, and put the filename as a column within the table.

I am new to using the DTS packager, and this looks like the best way to do what I need, but I have no idea where to start, I have looked at various tutorials on the internet and am trying to get this one to work.[url]http://www.databasejournal.com/features/mssql/article.php/1461661[/url]

But it is coming up with errors in the first script at the line

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(sFolder)

The other thing is, this way of doing it doesn't look like it will import the filename..

Is there a better way of doing the import, or is the above link a good starting place.

Thanks.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-03-15 : 05:22:28

Write the filename to a global variable in the loop:

DTSGlobalVariables("MyFileNameVariable").Value = fsoFile.Name

You should then be able to use this as a source for a column transformation on the datapump task (it's been a while since I've used DTS, so I can't be more specific!)
What error are you getting from the fso stuff?

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 05:24:36

This might give you an insight how to get the files you want

CREATE TABLE	#Files
		(
			FileID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			FileName VARCHAR(256)
		)

INSERT	#Files
	(
		FileName
	)
EXEC	xp_cmdshell 'dir "c:\windows\system32\*.*" /b /s /a:a'

DELETE
FROM	#Files
WHERE	FileName IS NULL

SELECT * FROM #Files

DROP TABLE #Files

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 05:31:42

Or this

CREATE TABLE	#Files
		(
			FileID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			FileName VARCHAR(256)
		)

INSERT	#Files
	(
		FileName
	)
EXEC	xp_cmdshell 'dir c:\windows\system32\*.* /b /s /a:a'

DELETE
FROM	#Files
WHERE	FileName IS NULL

DECLARE	@FileID INT,
	@bulk_cmd VARCHAR(1000)

SELECT	@FileID = MAX(FileID)
FROM	#Files

WHILE @FileID > 1
	BEGIN
		SELECT	@bulk_cmd = 'BULK INSERT {DestinationTableNameHere} FROM ''' + FileName + ''''
		FROM	#Files
		WHERE	FileID = @FileID

		--EXEC	(@bulk_cmd)
		PRINT @bulk_cmd

		SET	@FileID = @FileID - 1
	END

DROP TABLE #Files

Peter Larsson
Helsingborg, Sweden

boogybaz
Starting Member

8 Posts

Posted - 2007-03-15 : 06:03:59

Thanks Peso, playing with the code now
The error from the FSO object is

"Error Code0:
Error Source = Microsoft VBScript Runtime Error
Error Description: INvalid procedure call or argument

Error on line 19"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 06:05:20

What do you think BULK INSERT do?

Peter Larsson
Helsingborg, Sweden

boogybaz
Starting Member

8 Posts

Posted - 2007-03-15 : 06:09:43

I just saw that, and edited reply to reflect... Sorry Peso :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources