Dynamically Change the SQL Command in OLE DB Command Transformation in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

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

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



Script used for the video demo for Dynamically Change the SQL Command in OLE DB Command Transformation in SSIS Package


USE [Test]
GO

--Create Sample Tables for SSIS Package to use in OLE DB Command Transformation Demo
CREATE TABLE [dbo].[Customer_AS](
 [CustomerId] [int] NULL,
 [CustomerName] [varchar](100) NULL,
 [StreetAddress] [varchar](100) NULL,
 [City] [varchar](100) NULL,
 [State] [char](2) NULL
)

CREATE TABLE [dbo].[Customer_AS_Hist](
 [CustomerId] [int] NULL,
 [CustomerName] [varchar](100) NULL,
 [StreetAddress] [varchar](100) NULL,
 [City] [varchar](100) NULL,
 [State] [char](2) NULL
)


CREATE TABLE [dbo].[Customer_NA](
 [CustomerId] [int] NULL,
 [CustomerName] [varchar](100) NULL,
 [StreetAddress] [varchar](100) NULL,
 [City] [varchar](100) NULL,
 [State] [char](2) NULL
)

CREATE TABLE [dbo].[Customer_NA_Hist](
 [CustomerId] [int] NULL,
 [CustomerName] [varchar](100) NULL,
 [StreetAddress] [varchar](100) NULL,
 [City] [varchar](100) NULL,
 [State] [char](2) NULL
)


--Select Queries for checking data in SQL Server Tables
Select * From dbo.Customer_AS
Select * from dbo.Customer_AS_Hist
Select *  from dbo.Customer_NA
Select * from dbo.Customer_NA_Hist


--Update Statement with Parameter for OLE DB Command Transformation
update dbo.Customer_NA_Hist
set CustomerName=?,
StreetAddress=?,
City=?,
State=?
where Customerid=?


--Expression used in SSIS Package for OLE DB Command Transformation SQLCommand
"update "+ @[User::FileName] +"_Hist
set CustomerName=?,
StreetAddress=?,
City=?
where Customerid=?"

File Connection Manager Expressions
@[User::FolderPath]+"\\"+ @[User::FileName]+".txt"





Dynamically change the SQL Statement in OLE DB Command Transformation in SSIS Package



  Related Posts / Videos on OLE DB Command Transformation 


1 comment:

  1. Kudos to the team of techbrothers for putting up blog post about various aspects of SSIS every now and then and also researching ahead to know more about it.

    SSIS Upsert



    ReplyDelete