Import a CSV text file into SQL Server using DTS

[davelaws]

Hi,

This may be a silly question but im gonna ask it anyway!

What i need to do is execute a DTS package from a CF page to read a text file and import its content to a table. I have 100's of txt files with 10,000's of rows.

The reason i think this is a silly question is because when setting the DTS package in SQL Ent, you have to specify the file name of the file you wish to import data from, therefore, its possible that you cannot change the file name. Ive done lots of research and not found one example that will allow me to do dynamically change the file name making the DTS pick up a different file. The only code ive found is as below, that executes the DTS and imports the data obtained from the file name that was set when the DTS package was created.

<cfobject

type="COM" name="objDTS" class="DTS.Package" action="CREATE">

<CFSET

r = objDTS.LoadfromSQLServer("[SERVERNAME]","[PASSWORD]","",0,"","","","[DTSPACKAGE]","")>

<cfset

p = objDTS.Execute()>

I found the following suggestion in a forum somewhere, but ive not had much luck with it

<cfset

objDTS.GlobalVariables.Item("filename").Value = "[statsFileName]">

I know an alternative to this is to read the file using CFfile or using Java and insert one by one, i dont dont like that method as it could cause performance issues with my server.

Hope someone can help me out.

regards

d

[Dan_Bracuk]

The first thing that popped into my mind was to use cffile to rename the existing file to whatever the dts package needs.  Depending on the rest of your requirements, you may or may not need to make a copy of the original file, which you can also do with cffile.

[davelaws]

That has crossed my mind but if i can avoid using the cffile option ill be happy because i have 100's of files to deal with. I want to keep the originals just in case, which means having to duplicate each file before getting the DTS to do its job then delete them.

Is there no way of changing the the file name thats in the DTS pragmatically?

[JR__Bob__Dobbs-qSBHQ2]

Is there no way of changing the the file name thats in the DTS pragmatically?

This is hard to answer without access to your DTS package.  If the file name is a variable you might use the /A switch of the dtsrun utility to set a variable's value.

It might be better to ask this question in a SQL Server specific forum.

[JR__Bob__Dobbs-qSBHQ2]

Questions:

1. What version of SQL Server are you using?

2. Are you required to use a specific DTS package or do you simply need to import data from a file to a table?  You might also consider using the bcp utility or, if you are using 2005 or newer, integration services.

3. Is the uploading of files and the import of data a manual process or is it automated?  Is there a user uploading one file at a time to your server or do you receive files in batches.  Can you describe the work flow?

You might try the following if you are required to use DTS:
1. Upload your text file with CFFILE, putting in the directory expected by your DTS package.

2. Rename the file to the name required by your DTS package

3. Use CFEXECUTE to run the DTS package by calling dtsrun at the command line.

dtsrun
http://msdn.microsoft.com/en-us/library/aa224467%28SQL.80%29.aspx

bcp
http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx

[davelaws]

1. Im dealing with a mixture of 2000 and 2005 SQL servers

2. The reason i chose DTS is because i know its compatible with both 2000 and 2005. I dont want to deal with mix options.

3. The files already reside on the servers, they are visitor stats which have been collected in txt files over the past 10 years. I know, why would one have done this in the first place? Im just picking up the pieces! As i explained in Dan's post (previous post) i dont want to deal with cffile if at all possible.

The code ive posted kinda does what you've suggested, but not including the renaming of the required files.

As a note, we're moving away from storing data in txt files, which mean the inserting data into DB will a one off but for 100's of files over many sites hence my dilemma with performance issues. If it was once a day task i wont even care about the performance issue.

What i need is a solution for changing the file name thats assigned in the DTS. Any ideas?

Thank you both for your input so far.

d

[-__cfSearching__-]

2. The reason i chose DTS is because i know its compatible

with both 2000 and 2005. I dont want to deal with mix

options.

If you are not fixed on DTS, have you looked at BULK INSERT? I believe it is compatible with both.

[Dan_Bracuk]

Have you considered something like this?

create an empty file with the name you want

use cfdirectory to create a query object listing all the files.

loop through that query and append all the files in question to the one you just created.

use the new file with the dts package.

do what you need to do with all the files.

[-__cfSearching__-]

loop through that query and append all the files in

question to the one you just created.

Depending on the file sizes, you could end up with one massively big file. Not to mention doing unnecessary i/o.

As my dts is rusty, I would ask what is the correct way to do it in a sql server specific forum. Either that or use something simpler.

[davelaws]

Thank you guys for your help, i almost did  go down a simpler route but my conscious wouldn't allow me knowing the code may cause performance issues when reading txt files.

So, after lots of hours research ive come up with the following, hope it helps someone in the future.

My txt file
col1Txt,col2Int
|text 1|,|1|
|text 2|,|211|
|text 3|,|191|

--Create a temp table
CREATE TABLE ##myTempTable (myTextCol varchar(50), myIntCol varchar(50))

--The following would read the columns including the pipe delimiters
--DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ('bulk INSERT  ##myTempTable FROM "' + @filename + '"  WITH ( FIELDTERMINATOR = '','', FIRSTROW=2, ROWTERMINATOR =  '''+CHAR(10)+''')')
EXEC (@bulk_cmd)

--The following would take out the unwanted pipes and CR's
UPDATE ##myTempTable
SET myTextCol = replace(LTRIM(RTRIM(myTextCol)),'|',''),
myIntCol = replace(replace(myIntCol,'|',''),CHAR(13),'');

I tested this using a file with over 350K text rows, the import took less then 1min plus i had more code in addition to the above.

thanks again.

d