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
Script used for the video demo for Dynamically Change the SQL Command in OLE DB Command Transformation in SSIS Package
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
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
- Introduction to OLE DB Command Transformation( Perform Update/Delete)
- How to use Stored Procedure Output Parameter in OLE DB Command Transformation in SSIS Package?
- OLE DB Command Transformation - Insert Operation Demo
- OLE DB Command Transformation - Delete Operation Demo
- OLE DB Command Transformation - Update Operation Demo
- OLE DB Command Transformation - Call Stored Procedure with Input Parameters Demo
- OLE DB Command Transformation - Use Sub Query with Parameters in OLE DB Command Transformation to update records in a Table
- OLE DB Command Transformation ( Use Common Table Expressions to Delete Duplicate Records in OLE DB Command Transformation with Input Parameters)
- OLE DB Command Transformation ( How to Call Multiple Stored Procedure with input parameters in OLE DB Command Transformation in SSIS Package)
- OLE DB Command Transformation ( How to Run Multiple Statements Update/Insert/Delete with parameters in OLE DB Command Transformation in SSIS Package)
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.
ReplyDeleteSSIS Upsert