asked on

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

Hello,

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?

Thanks.

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

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
as

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

kaminda

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.

http://msdn.microsoft.com/en-us/library/ms169917.aspx

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

Alpesh Patel

Use for each loop and transfer processed file to archive folder
in that you can give a *.txt or any filter.