Importing Multiple CSV Files Using DTS

[akki007]

I have 116 CSV files in a directory. They are all in exactly the same format. I need to import them all into a table via DTS. How can I do this in one package?

Thanks

 

[NaoTriste]

Well, I don't think you can multi-select like you can with tables but you can copy and paste the source connection. in this case being the text file.
So basically you would have to :
Create the Source Connection of text file type
Create the Destination connection as Microsoft OLE DB Provider for SQl Server
Do ctrl+v 116 times , one for each CSV file
Change the name of the CSV file as appropriate and then create 116 transformations all into the same destination connection.

Actually that's quite a crap, laborious solution, can you not join all the CSV files together considering that they have the same no. of columns ? Save it as an xls and then do it in one single transformation.
Nahh...I am going to check...

 

[akki007]

Im getting 116 files every week. I dont want to waste time concatenating them all!! There is a way of doing it using global parameters, I just dont know how.

 

[Dimandja]

Create batch files.

Get the instructions here:

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

 

[NaoTriste]

Oh ok. I don't really think you want to use Global variables . What you want is the Dynamic properties task which allows you to set any task properties, such as the connection text file data source value to an entry on a ini file, Global variable (no point) and various other options.
Drag that task to the workspace , click on Add , expand connections , expand Text file , expand OLE DB Properties , choose Data Source . click on set and the rest is self explanatory.

 

[yksvaan]

How about, what I did, if you don't have column headings in the files (or you can get rid of them) copying the files into a single file with xcopy (or equivalent). You can start this with the Execute Process task.

Also, You can make loops in a package (done it), but it is not self explanatory. It that case Global variables will be used. Check bol and

http://www.sqldts.com

has also valuable information.

Cheers

 

[dky1e]

Create DTS package, call it YOURDTSNAME. Create two connections, one pointing to your file and call it 'src' and another one to the server, create a data pump task and set it up accordingly.

Then dynamically change and source file name and execute the package using the code below:

declare  @hr 		int
	,@dts 		int
	,@src		int
	,@dataSource 	varchar(50)
	,@SrcFolder	varchar(255)
	,@buffer	nvarchar(4000)
	,@object 	int

create Table #tFiles
(FileName varchar(100))

set @SrcFolder = 'c:\folder\source'


--get files in the import folder
set @buffer = 'dir ' + @SrcFolder + ' /B'
insert into #tFiles 
exec master..xp_CMDShell @buffer
delete from #tFiles where len(IsNull(FileName,'')) < 1

exec @hr = sp_OACreate 'DTS.Package', @dts out
if @hr <> 0 begin set @object = @dts goto abt end

exec @hr = sp_OAMethod @dts, 'LoadFromSQLServer',NULL, @ServerName = @@SERVERNAME,
		@PackageName = 'YOURDTSNAME', @Flags = 256
if @hr <> 0 begin set @object = @dts goto abt end

exec @hr = sp_OAGetProperty @dts, 'connections("src")', @src out
if @hr <> 0 begin set @object = @dts goto abt end

if right(rtrim(@SrcFolder),1) <> '\'
	set @SrcFolder = @SrcFolder + '\'

--clear old data
delete from dbo.tmpMotorIntelligenceStage
--for each file import data
while exists(select 1 from #tFiles)
begin
	select top 1 @buffer = FileName from #tFiles
	delete from #tFiles where FileName = @buffer
	select @buffer = @SrcFolder + @buffer

	exec @hr = sp_OASetProperty @src, 'datasource', @buffer
	exec @hr = sp_Oamethod @dts, 'execute'

end

goto EXT

ABT:
exec sp_OAGetErrorInfo @object

EXT:
exec @hr = sp_OADestroy @src
exec @hr = sp_OADestroy @dts
drop table #tFiles