Import flat file automatically?

Hot Topic

Should I use Notifications for this?
RS 2005 Install after 2K Removal
Certificate limitations in SQL Server 2005
Help in Moving RS databases to another machine
Select statement with dynamic columns based on data content
Intercept Result
SP_SETAPPROLE - Get Sql Err Message 15422 when activating application role
Tag name from an XML
Pre-populating date dimension!!!
Synchronize data between servers

SQL Server

Is SSIS slower than DTS????!!!!!!
Registering SQL 2005 Server Evaluation
Where have the samples gone?
Attribute Relationships in Project Real
file attribute using a control
SQL Deployment / Production Package Versioning
Email not being sent...
Can not connect to a flat file
xpsendmail and table not found
SQL Server 2005 Express: Weird error message running SELECT COUNT query

Import flat file automatically?

Hi,

I'm going to be getting several flat files that need to be imported into one of two tables. Although the text files will have different file names, they will have either "Header" or "Detail" in the file name, so I can tell which table they need to be imported into.

The problem is I don't know enough about SQL Server 2005 to set up an automated import of these files into the database. Does anyone have any suggestions on how to do this, or have any experience in setting this up Are there any inexpensive programs to load data realtime or on a schedule (I work for a young company with a very tight budget).

I had an idea of creating a windows vb or batch program to create the import commands for each of the flat files, but I can't find the line command to build the import command.

I'm just at a loss and need a solution soon...

Thanks,

Laura

Answer this question

Import flat file automatically?

  • musicalglass

    Thanks for your response, but I have a few more questions.

    I'm unfamiliar with all the SQL Server acronyms, so can you tell me what BCP and SP's are

    I've looked at SSIS, but can the flat file have different file names for the import I know when I used DTS, I needed to choose a specific file name. The only thing consistent in the file name will be the word "Header" or "Detail" (i.e. cust1-cust2-Header.txt). Also, will SSIS be able to perform the import on multiple flat files Depending on each run, there may be as few as 1 or as many as 500 or more files that need to be imported.

    This is also going to be an ongoing process, probably running every 15 minutes or so. Which of the above solutions would be most efficient

    Thanks,

    Laura

  • James Christian

    Thank you all for your help. I also searched the website you sent me to, and there was example code at the following address, that is exactly what I was looking for!

    http://www.nigelrivett.net/ImportTextFiles.html

    I'm going to schedule the running of this procedure in ssis and it should work just great.

    Thanks again!

    Laura

  • Kiz

    There are many ways to do this.
    1. Write a CMD script that uses BCP to import the data files
    2. You can write SPs in the backend that imports the files directly using BULK INSERT or OPENROWSET(BULK) in SQL Server 2005
    3. Use DTS in SQL Server 2000 or SSIS in SQL Server 2005 to automate the import process
    4. You can use the SQLDBO BulkCopy object in SQL Server 2000
    5. You can use the Sql BulkCopy API in .NET 2.0 (SQL Server 2005)
    So it depends on the level of complexity, your requirements and the amount of coding you want to do etc. Please take a look at the options and see.


  • Gadget_IOM

    Hi...

    SSIS has a looping construct that will allow you to loop trough all files in a directory and process them. So the actual name of the file can be determined by this looping construct (For each File it is called I think)



  • nwilliams

    SP Stands for Stored Procedure while bcp is a utility (bcp.exe), a command-line tool that is written using the bulk import API. The bcp utility performs the following tasks:
    • Bulk exports data from a SQL Server table into a data file.
    • Bulk exports data from a query.
    • Bulk imports data from a data file into a SQL Server table.
    • Generates format files.

    For step-by-step on how to import flat file check out this following links:

    http://www.sqlteam.com/item.asp ItemID=3207

    http://www.sqlteam.com/item.asp ItemID=4722



  • Import flat file automatically?
Answer this question