SSIS Tutorial Part 51-Dynamically Change the SQL Command in OLE DB Command Transformation

We have multiple tables with same structure for customers for each region. We have this requirement to build an SSIS Package that will read the data from flat file and then load the data depending upon the name of file. After loading the data to specific table, it will also update the records in history table. History Table follow the same meta data and name has _Hist at the end.

What you will learn by watching this video

How to create SQL Server Tables by using Create Statement
How to Insert Rows for testing purpose in SQL server Tables
How to Create an SSIS Package from basics
How to use For-each Loop to read Multiple Files from a Folder Path Variable
How to use Data Flow Task to read Flat file Source And perform Data Conversion
How to use Variable in OLE DB Destination to change the name of Destination table dynamically
How to Change the update Query for OLE DB Command Transformation Depending upon File Name
How to write Expressions on Data Flow Task as well on Flat File Connection Manager

Blog post link for scripts used in this video

Check out our Full Step by Step SQL Server Integration Services(SSIS) Tutorial

[rashmi]

Thankyou so much Amir for educating us, because of you i am selected for MSBI Project . Again thanks a lot Sir.

Regards

[fauziabash]

Thank you for your long video. it has more information that help us people like me who are new in this field. And appreciate your help. Thank you once again

[devexpost]

*The Key Parts:*

1. In the Foreach Loop Editor on the Variable Mapping pane, create a new Variable called User::FileName, mapped to Index=0. As the Foreach Loop iterates over the files in the Directory, it places the name of each current file in this variable name. This is where the dynamic aspect of the package is created and dynamically altered.

2. Within the Data Flow Task, Flat File Source, Flat File Connection Manager Properties, set Property “ConnectionString” to an Expression like: @[User::InputFilePath] + ”\\” + @[User::FileName] + ”.txt” ... This is what creates the dynamic selection of the input files within the loop for the Flat File Source.

3. Within the OLE DB Destination under Data Access Mode select “Table name or view name variable” and in Variable Name select User::FileName. This is what creates the dynamic selection of the database tables within the loop for the OLE DB Destination.

One would think that a dynamic expression for the OLE DB Command would be set up in its own Properties, but not so...

4. Go instead to the Data Flow Task’s Properties, Misc->Expressions, and open the Property Expressions Editor. In its Property list there will appear a property called “[OLE DB Command].[SQLCommand]” (or whatever name you changed to for the ‘OLE DB Command’ transformation name); select it and build the dynamic SQL statement as its Expression... Open the Expression Editor and enter like: “UPDATE “ + @User::FileName + ”_History” + ” SET Column1=?, Column2=? WHERE Column3=?” ... This is what creates the dynamic selection of the database tables within the loop for the OLE DB Command.

Best Regards.
.

[kdataengineer]

what to do if the structure of the files are different, lets say the two files in the folder are CUSTOMER and ORDERS, Please reply sir :)

[prramesh]

How to do the same thing when having different files with different columns?

[rashmi]

Thankyou so much Amir for educating us, because of you i am selected for MSBI Project . Again thanks a lot Sir.

Regards