Working with flat files dynamically in SSIS package

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Problem:

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
source.

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

Solution:

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
    ReadOnlyVariables.
  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::DirPath"].Value.ToString()
    + "\\" + 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
    =Dts.Variables["User::DestinationPath"].Value.ToString()+"\\base.txt";
  13. Dts.Variables["User::FilePathAfterRename"].Value =
    Dts.Variables["User::DestinationPath"].Value.ToString()
    + "\\" + Dts.Variables["User::Year"].Value.ToString() +
    Dts.Variables["User::Month"].Value.ToString()+".txt";
  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

Plan

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply