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

Posted on 2011-05-11
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

    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 39

    Expert Comment

    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

    LVL 9

    Expert Comment

    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
    Use for each loop and transfer processed file to archive folder
    in that you can give a *.txt or any filter.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now