Working with flat files dynamically in SSIS package

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";
15. Create a File System Task
    and name it “rename file”
16. Set operation as Rename
    File and set “FilePathBeforeRename” as source variable and use “FilePathAfterRename”
    as destination variable.
17. Create a Data Flow Task
18. Create a Flat File
    connection with source of your template file and configure it
19. Go to Flat file connection
    properties window in expression and set connectionString with @[User::FilePathAfterRename]
20. Use this Flat file
    connection in a Flat File Destination in DataFlow and transfer your data there.
21. 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

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: https://radacad.com/blog.