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


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?


Who is Participating?
8080_DiverConnect With a Mentor Commented:
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.
lcohanDatabase AnalystCommented:
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

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.
Alpesh PatelAssistant ConsultantCommented:
Use for each loop and transfer processed file to archive folder
in that you can give a *.txt or any filter.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.