connection manager in SSIS

Posted on 2011-04-27
Last Modified: 2013-11-18
I'm creating an SSIS package in Business Intelligence  Studio which  takes a csv file and loads it into a database table.
The csv file is dumped on a network share on a weekly basis.
I want to schedule a job that automatically runs an SSIS package that loads this file into the database.

The problem is that the name of the file changes on a weekly basis

Is it possible to  create a Connection Manager that can accomodate these file name changes?
Question by:blossompark
    LVL 21

    Accepted Solution

    Create File Connection runtime using Expression.

    "\\\\" +  @[User::FILE_SHARE] + TRIM(@[User::Done]) + LTRIM(@[User::FileName]) + (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) +  RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) +   ".txt"
    LVL 15

    Assisted Solution

    if all the files are in one directory

    you can loop through all files in the directory and copy them to sql using Foreach Loop Container
    LVL 15

    Assisted Solution

    here is another useful article

    Using SQL Server Configurations to configure Flat File Connection Manager

    Author Closing Comment

    Hi PatelAlpesh  and  AmmarR, thanks for your responses...that gives me plenty to work with...Thanks again :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
    In this article I will describe the Backup & Restore 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.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now