Skip to main content
D
davelaws
Participating Frequently
November 11, 2009
Question

Import a CSV text file into SQL Server using DTS

  • November 11, 2009
  • 2 replies
  • 1955 views

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

    This topic has been closed for replies.

    2 replies

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 11, 2009

    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

    D
    davelawsAuthor
    Participating Frequently
    November 11, 2009

    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__-
    Inspiring
    November 11, 2009

    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.

    D
    Dan_Bracuk
    Inspiring
    November 11, 2009

    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.

    D
    davelawsAuthor
    Participating Frequently
    November 11, 2009

    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?

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 11, 2009
    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices