Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Dynamically set Flat File Connection Manager

Mar 14 2010 12:09PM by Sudeep Raj   

Hi,

In the SSIS forum very often there are questions on dynamic name for a flat file connection manager

I have split it in two parts:

  • A single file name needs to be set up dynamically
  • Multiple file names need to be set up dynamically per execution.

In this post I will talk about the first scenario.


Scenario:

For simplicity sake I will be using a case where the file name is saved in a DB table.

Need to fetch the file name from a database table.

Need to use a Data Flow Task which has a Flat File Source.

This flat file source needs the file name to be set up dynamically at run time.

The folder for the file is fixed.





Create a string Variable: FileName with the scope as the package(Practice in my case). Set the value of the variable to a file name that exists (I set it to Demo.txt) just while development.




Now set up the Flat File Connection Manager for the above file that exists and has the same meta data as the file that will be used later.


File name: Click on the Browse button and select the file.

I am using the other default settings.





Click on the Columns tab to verify the columns.

Then click OK.




To set up the Execute SQL task to fetch the file name and save it to a user variable FileName.

In the Execute SQL task Editor window go to the General tab.

Do the following settings:
ResultSet : Single row.

ConnectionType : OLEDB(that’s what I have used you could also use ADO.NET)
Connection : Select the appropriate one.
Put the query to get the single file name from the table.



Now select the Result Set tab

Click the Add Button.

You will see the below screen with a row for the result details.

In the Result Name set it to : 0 (Numeric zero)

In the Variable Name select the string type variable which has the scope set to the package.

Once this is done click OK.




Now select the Flat File Connection Manager 1 and click "F4" or right click and select “Properties”.




In the properties pane for the above connection manager:

Set the DelayValidation to True

Expand the Expressions by clicking the ellipse.




On the Property Expressions Editor window:

In the Property column select the ConnectionString property from the drop down list.

Now expand the Expression column by clicking the ellipse. An Expression Builder window will pop up.




On the Expression Builder window:

Set the Expression as:

“C:\\Data\\” + @[User::FileName]

Click the Evaluate expression button at the left hand bottom to see if the expression is creating the desired output.

Click OK.

Click OK on the Property Expression Editor as well.




Now set up the Data Flow Task as below:




Double click the Flat File Source.

In the Flat File Source Editor:

In the Connection Manager tab, select the appropriate Flat file connection manager from the drop down list.




Now in the OLEDB Task:

Select the OLEDB Connection needed to connect to the destination Data base.

Select the table where the columns need to be inserted.




Goto the mapping

Go the mapping and do the mapping as required and click OK.




Save and build the package.
Now when you execute the package the package will take the filename from the DB table.

This could also be modified by having the file name stored directly to a variable and make this variable configurable.

Very soon I will be posting on setting up file names dynamically for multiple files.

Please provide your valuable feedback.


Tags: SSIS, Expressions, Substring, FindString, MSBI,


Sudeep Raj
12 · 13% · 4302
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

20  Comments  

  • Very well explained !!..Good work Sudeep !..

    commented on Jul 5 2011 2:10PM
    Sana ali
    165 · 1% · 297
  • Good article.... can you please upload Packages and Data you are using for your samples ... it will be great

    commented on Sep 6 2011 8:44PM
    vishalchitrala@gmail.com
    287 · 0% · 148
  • This is exactly what I had been searching for. The part I had been missing was the properties for the flat file connection manager: "Delay Validation" and setting the ConnectionString expression. Thanks so much for posting it in detail.

    commented on Nov 7 2011 2:01PM
    KathyPDX
    2726 · 0% · 4
  • I'm extracting to a flat file. This almost worked for me. The resulting flat file does not have the '.txt' extension. 'Africa-SouthAfrica-Consolidated-CMMIS-Maintenance-Equipment-2011-11-09' should be 'Africa-SouthAfrica-Consolidated-CMMIS-Maintenance-Equipment-2011-11-09.txt' The connection manager has '.txt' in the file name: 'Equipment_Extract.txt'. Why doesn't the final file?

    commented on Nov 9 2011 12:34PM
    clhudson47
    2726 · 0% · 4
  • @clhundson47: Check the file name that you have in the table. Does the file name contain .txt?

    commented on Nov 9 2011 1:27PM
    Sudeep Raj
    12 · 13% · 4302
  • It does now. Thanks!

    commented on Nov 9 2011 2:13PM
    clhudson47
    2726 · 0% · 4
  • Sedeep, You said above "Very soon I will be posting on setting up file names dynamically for multiple files." Did you ever do that? I looked through your post titles and didn't think I saw it. Thank you.

    commented on Nov 21 2011 6:27PM
    KathyPDX
    2726 · 0% · 4
  • Hi Kathy, Have a look at this: http://beyondrelational.com/blogs/sudeep/archive/2009/11/19/dynamic-file-connection-in-ssis.aspx

    commented on Nov 24 2011 9:35AM
    Sudeep Raj
    12 · 13% · 4302
  • I have to dynamically create the SSIS connection string to a Flat File connection with a UNC path because if the computer specified in the 1st connection string is down, I want to continue running the package with the 2nd connection string. I have created a package scope string variable named ConnectionString with an initial value = \COS-FIDS-01\~fidsdatascript$\data.txt.

    I have a table that contains 3 rows, 1 for each computer and Execute SQL tasks to assign the values in the table to the string variable named ConnectionString. I have set breakpoints before and after the 1st Execute SQL task, with the value in the 1st row of the table = \AirTacknical\~fidsdatascript$\data.txt.

    When I inspect the breakpoint before the 1st Execute SQL task ConnectionString = \\COS-FIDS-01\~fidsdatascript$\data.txt, with every \ doubled to \

    When I step over the 1st breakpoint and execute the 1st Execute SQL task ConnectionString = \\AirTacknical\~fidsdatascript$\data.txt It looks like this is the required behavior for the ConnectionString variable to be assigned to the Flat File Connection Manager’s ConnectionString property using the Property Expressions editor as you show in your example. When I open the Expression Builder as you show above and click on Evaluate Expression I return \COS-FIDS-01\~fidsdatascript$\data.txt

    My package checks the connection string for the 1st row in the table using a File Properties Task I downloaded from CodePlex, but it appears to check using the value of the ConnectionString variable with the extra \’s

    How can I have another variable that avoids the doubling of the \ character so my File Properties Task can succeed?

    commented on Dec 1 2011 12:55PM
    phorne
    3016 · 0% · 2
  • I have a scenario hope you can be helpful I need to create a flat file for each run and file name will be something like FileYYYYMMDD A.csv the next file will be File YYYYMMDD B.csv The thing is every consecutive file created for individual run (it is something like need to check if file exist with letter A create with letter B ) will have a alphabet appended to the file name after the datestamp if 3 fiels are generated on a given day like today it have to File20121022A.CSV, File20121022B.CSV, File20121022C.CSV

    Thanks,

    commented on Oct 22 2012 12:48PM
    ssyed
    2803 · 0% · 3
  • Hi Syed,

    Is it that you want A, B, Can and so on or can you use Numbers instead? Do you have access to database to create control tables? This table could keep the file name created along with the sequence number and the date in separate columns. Now you can read the data from the table before creating the file.

    commented on Oct 22 2012 1:42PM
    Sudeep Raj
    12 · 13% · 4302
  • Thanks for the quick reply , no i cant use the numbers I am not sure but let me check if i can create control tables .

    commented on Oct 22 2012 3:09PM
    ssyed
    2803 · 0% · 3
  • Hi ssyed, I have various fixed width format files from clients that all have same length (e.g. 700) and have same column names all with same lengths (e.g. firstname: 8, lastname:10,). The only difference is that some of the files come with the columns on different positions. Instead of creating different packages, I would like to set it up where the connection manager is chosen according to the client's name which can be determined by the filename. Is this possible at all to change the connection manager used in a data flow task?

    Thanks,

    commented on Jan 2 2013 10:53AM
    pedero
    2803 · 0% · 3
  • Hi Pedero,

    This is not possible to do in one Data Flow Task. What you can do instead, is create multiple Data Flow Tasks for each connection you have(should not be many). Now based on the Flat file being processed use precedence constraint to execute the right Data Flow Task.

    commented on Jan 2 2013 12:03PM
    Sudeep Raj
    12 · 13% · 4302
  • Thanks Sudeep for the quick response. I looked up precedence constraint and it should work great for what am doing.

    commented on Jan 2 2013 1:49PM
    pedero
    2803 · 0% · 3
  • Hi,

    I'm pretty new to SSIS. Is it possible if I already have those existing text files (apoints.txt, bpoints.txt, cpoints.txt) and then create those same file names in excel so they will be like this: apoints.xlsx, bpoints.xlsx, cpoints.xlsx and so on? I already have Foreach loop container and I placed the data flow task inside it. On the data flow, I have a dynamic flat file source connected to data conversion then derived column and finally excel destination. I also added a variable to the watch list, it successfully loops through text files and writes each output to an excel file (I also tried to make this dynamic but no success so far) but I want multiple outputs. For example, each ouput from apoints.txt flat file source will go in apoints.xlsx excel destination, the next file bpoints.txt will go in bpoints.xlsx...any idea? i need help...i've been struggling with this for a couple of weeks now.

    commented on Mar 27 2013 1:37PM
    kpann
    2803 · 0% · 3
  • Hi Kpann,

    You need to set up the destination connection manager dynamically as well the way you set up the source connection. your expression would be something like:

    "C:\YourFolderPath\" + @[User::FileName] + ".xlsx"

    commented on Apr 5 2013 3:38AM
    Sudeep Raj
    12 · 13% · 4302
  • Hi

    I have problems with the sql Statement I have the query like this:

    Select SourceCertificados from File

    not working

    commented on Jun 25 2013 10:48PM
    oscarsteeven
    3016 · 0% · 2
  • What is the error you are getting? What are you trying to do?

    commented on Jun 27 2013 3:42PM
    Sudeep Raj
    12 · 13% · 4302
  • This comment is waiting for moderation.

    commented on Apr 21 2014 6:10AM
    prashanth00789
    3016 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SSIS - Dynamically set Flat File Connection Manager" rated 5 out of 5 by 4 readers
SSIS - Dynamically set Flat File Connection Manager , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]