Importing records from a text file to a SQL table.  Text file name changes daily

Posted on 2011-05-11
Medium Priority
Last Modified: 2012-05-11

I would like to create a SSIS package in SQL Server 2005 that will read records from a text file in a specified folder.  This will insert all records from that text file into a table.

The problem is that the name of the text file willl change daily.  The only  part of the name that will not change is the first  6 characters.  If I could call it by using something like  cusers*.txt it would help.

I know how to generate a SSIS package using the import data wizard and choosing a flat data source, but I am not that familiar with tweaking the SSIS package to use a wildcard in the name.

Any ideas?


Question by:TheUndecider
LVL 22

Accepted Solution

8080_Diver earned 2000 total points
ID: 35742615
You will need to develop the SSIS package in BIDS (Business Information Development Studio . . . you should be able to do this from VS).  You will need to use a File System Task to find the files to be processed and then you will need to create a For Each container to process each of the files you have found.

However, if your level of expertise with SSIS packages is limited to using the Data Import/Export Wizard, this may be rather challenging.
LVL 40

Expert Comment

ID: 35743417
Or simpli by running some SQL like the one below and just remember the file path is relative to the SQL server computer not client where you run it from:

--Usage : exec sp_readTextFile 'c:\test_file.txt'
Create proc usp_readTextFile @filename sysname

    set nocount on
    Create table #tempfile (line varchar(8000))
    exec ('bulk insert #tempfile from "' + @filename + '"')
    select * from #tempfile
    drop table #tempfile


Expert Comment

ID: 35744413
Here is a very helpful step by step article from MSDN on how to use BIDS to create a SSIS package. It is doing the same thing as your requirment. This was my first tutorial to follow on SSIS and it gave me a wonderful start.


Go through the adding looping section to find out how you can give name with wildcard characters to get the filename dynamically.
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35745760
Use for each loop and transfer processed file to archive folder
in that you can give a *.txt or any filter.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question