Working with flat files dynamically in SSIS package


Create a directory based on each year in a specified path, and
then create a flat file named with compound of year and month based on a
template file. You need to transfer some data into this flat file from a

The above scenario is a usual scenario when you work with
flat files,

for example creating mainframe files. Challenges in this scenario are:

  1. creating file and folder name dynamically
    based on variables
  2. Create a Flat file
    connection is SSIS based on variable name


I suppose that you know everything about File System Task
and you can use it in a SSIS package correctly.

  1. Create a Script Task in
    your SSIS package name it “set folder path”
  2. Use “DirPath” (variable
    which maintain specified path for working folder) and “Year”(variable which has
    year in value, suppose this year set from outside of SSIS package) as
  3. Use “DestinationPath” as
    ReadWriteVariables. Path of generated folder will be stored here.
  4. Write this script in Main()
    method: Dts.Variables["User::DestinationPath"].Value =
    + "\\" + Dts.Variables["User::Year"].Value.ToString();
  5. Create a File System Task
    and name it “create folder”
  6. Use “DestinationPath” as
    source connection variable
  7. Create a File system Task
    and name it “copy file”
  8. Use “SourceFilePath”
    (variable which has path of source template file) as source, and “DestinatioPath”
    as destination variable
  9. Create a Script Task and
    name it “Set File Name”
  10. Use “DestinationPath” and “Year”
    and “Month” as ReadOnlyVariables
  11. Use “FilePathAfterRename”
    (for file path after renaming) and “FilePathBeforeRename” (for file path before
    renaming) as ReadWriteVariables.
  12. Write this script in Main() method: Dts.Variables["User::FilePathBeforeRename"].Value
  13. Dts.Variables["User::FilePathAfterRename"].Value =
    + "\\" + Dts.Variables["User::Year"].Value.ToString() +
  14. Create a File System Task
    and name it “rename file”
  15. Set operation as Rename
    File and set “FilePathBeforeRename” as source variable and use “FilePathAfterRename”
    as destination variable.
  16. Create a Data Flow Task
  17. Create a Flat File
    connection with source of your template file and configure it
  18. Go to Flat file connection
    properties window in expression and set connectionString with @[User::FilePathAfterRename]
  19. Use this Flat file
    connection in a Flat File Destination in DataFlow and transfer your data there.
  20. Run SSIS package

Two challenges above solved by:

  1. Using Script Task with File
    System Task for generating dynamic file and folder names
  2. Set connectionString in
    expression property of Flat File Connection Manager


Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply