none
Creating Dynamic Flat Files in SSIS

    Question

  • Hi,

     

       Can any one help in creating a dynamic flat file and building a connection to that flat file at the run time in SSIS?

    I am Using MS SQL SERVER 2005.

     

    Thanks  in advance.

     

    Rahul

    Monday, June 23, 2008 8:44 PM

Answers

  • You'll need to create a Flat File Connection Manager and you can point it to any flat file really, and then edit the Expressions and place your variable in the Connection String expression.  This will then create the flat file at run time based on the contents of your variable.

     

    Tuesday, June 24, 2008 5:50 PM
  •  RahulVasudeva wrote:

    I will try this... In the mean time can u please tellme how , Can I give the name of the files dynamically.

     

    Please see Chris's first response - the steps he lists there are how to solve this problem.

    Tuesday, June 24, 2008 6:26 PM

All replies

  • Dynamic Source or Destination?  You can't have dynamic metadata, unfortunately, but if all your source flat files have the same schema, then it's not an issue.

     

    1) Set up a string variable

    2) Assign a value to the variable using your business logic either in an Execute SQL Task or a Script Task

    3) Use this variable in the Connection String of the Flat File Connection Manager

     

    HTH,

     

     

    Monday, June 23, 2008 9:20 PM
  • Hi,

     

       Thanks for the reply. I have used a variable and assigned the path of the file to be created. but unable to create the file.

    Secondaly, once the file is created then I have to build a connection to that to puch data from my procedure. Can you send some sample ?

     

    Regards,

    Rahul

     

    Monday, June 23, 2008 9:39 PM
  • Are you trying to build the flat file metadata (column names, data types, etc.) dynamically, or just the file location?

    Monday, June 23, 2008 10:13 PM
  • Hi ,

     

    I am just trying to create flat file using SSIS package.

     

    Please reply...

    Thanks... Rahul

     

    Tuesday, June 24, 2008 5:40 PM
  • You'll need to create a Flat File Connection Manager and you can point it to any flat file really, and then edit the Expressions and place your variable in the Connection String expression.  This will then create the flat file at run time based on the contents of your variable.

     

    Tuesday, June 24, 2008 5:50 PM
  • Thanks Cris.....

    I will try this... In the mean time can u please tellme how , Can I give the name of the files dynamically.

    Lets say name of the file is ABC + CurrentDataTime .... as I have to schedule the package and create file and make a connection to the file and then pushing data from the database ?

     

    Thanks & regards,

    Rahul

     

    Tuesday, June 24, 2008 6:08 PM
  •  RahulVasudeva wrote:

    I will try this... In the mean time can u please tellme how , Can I give the name of the files dynamically.

     

    Please see Chris's first response - the steps he lists there are how to solve this problem.

    Tuesday, June 24, 2008 6:26 PM
  • Hi Rahul,
    I hope you have completed this by now. One of the sample expression might be:
    @[User::LogFileLocation] + @[System::PackageName] + Replace(REPLACE(  (DT_WSTR, 30) @[System::StartTime] ,"/","_"),":","_") + ".txt"

    Here I have used @[User::LogFileLocation] to store a logfile location.

    HTH!
    Rakesh

    • Proposed as answer by Rakesh Mishra Thursday, October 08, 2009 1:27 PM
    Thursday, October 08, 2009 1:25 PM
  • Hello All,

    I am using SQL Server 2008 and wants to loop thru multiple flat files in a folder using a for each loop.

    I want to give flat file connection manager , a dynamic connection string but when I try to set its expression for "Connection String", I am unable to get the list of user defined varaibales instaed I am getting only System defined variables. Can anyone help me with this?

    Thanks in advacnce.

    Sonal

     

     

    Wednesday, May 19, 2010 7:09 AM