Saturday, 18 November 2017

How to Update the Records in a Table using OLE DB Command Transformation in SSIS Package

SSIS Package to Update the Records in a Destination Table by Mapping with Source Table using OLE DB Command Transformation
Scenario :
Suppose we have the Source Table data which is in Sales Database is as follows..
Select * From [SalesDB].[dbo].[Tbl_Customers]



Now I want to Update the Customer details like Cust_Name and Cust_Segment from the above table into below Destination Table, which lies in the MyAnalytics Database as follows..
Select * From [MyAnalyticsDB].[dbo].[Tbl_Customers]

Now lets design the Package to accomplish the above Scenario
-----------------------------------------------------------------------------------------------------
Phase-1 : Create the  required OLDB Connections 
-----------------------------------------------------------------------------------------------------
Create the Connections to the Source ( SalesDB ) and the Destination (MyAnalyticsDB)

-----------------------------------------------------------------------------------------------------
Phase-2 : Add the Data Flow Task to Control Flow and Design the Flow
-----------------------------------------------------------------------------------------------------
A) Add the Data Flow Task

B) Add the OLE DB Source Transformation in Data Flow and Set Properties
Here I have connected to the Source Database "SalesDB" where my Source Table having Customer Details .



Next Review and Select the Columns from the OLE DB Source Editor > Columns Tab


Next say OK.
-----------------------------------------------------------------------------------------------------
Phase-3 : Add the OLE DB Command then Set the Properties
-----------------------------------------------------------------------------------------------------
A) Add the OLE DB Command Transformation and Connect to OLEDB Source Transformation


B) Next set Connection to the OLE DB Command Transformation from Advance Editor>Connection Managers
Here I have connected to the MyAnalyticsDB where I have destination Customers Table in which I want to update the Customers Details.


C) Next in the OLE DB Command Transformation>Advance Editor>Component Properties , update the SQL Command
Here I have used the below Update Statement with Parameters (? ).

Update [MyAnalyticsDB].[dbo].[Tbl_Customers]
Set Cust_Name=?, Cust_Segment=?
Where Cust_Id=?

The Value for each Parameter will be passed by Source Table, by Mapping it as in the Next step.


D) Next in the OLE DB Command Transformation>Advance Editor>Column Mapping, map the Source Columns with Destination Parameters.
Map the Correct Column with Corresponding Parameter.

Ignore the Input Output Properties Tab, nothing will update in this Tab.

Next say OK. Now the Package is Ready.

E) Finally Run the Package.

------------------------------------------------------------------------------------------------------
Output :
Now the destination Customers Table in the MyAnalyticsDB will updated with required Customer Details from source Customers Table of SalesDB

Select * From [MyAnalyticsDB].[dbo].[Tbl_Customers]


Important Notes :
OLE DB Transformation will performs the any Update/Delete operation on Row by Row basis.

For Large Datasets , this Transformation takes long time to Process. So in that case , its is advisable to load the Data to a Staging Table in the Data flow, next Connect this Data Flow to the Execute SQL Task in the Control Flow then perform the your Operations using SQL statements.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Example -II :
------------------------------------------------------------------------------------------------------
Executing a SP with Parameters in OLE DB Transformation :
Suppose we have an SP with 3 Parameters as follows...
SP Definition :
Create Procedure spUpdate_CustomerDtls
 (
  @CustId Integer,
  @CustName Varchar(50),
  @CustSeg Varchar(50)

 )
AS
BEGIN
 Update [Tbl_Customers]
 Set  [Cust_Name]=@CustName , [Cust_Segment]=@CustSeg
 WHERE  [Cust_Id]=@CustId
END
GO

We can execute this SP using the above OLE DB Command Transformation , by Passing the SP with 3 Parameters In the SqlCommand as follows..

Exec spUpdate_CustomerDtls ?,?,?

Next we need to Map the Source Columns to these @Parameters as explained in the above Example. Also I shown here for your understand..


The Remaining Steps we need to follow as explained in the above first Example


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------




How to Create Multiple Flat File Destinations for each Region Data from Sales Table Source of SQL Server using SSIS Package

SSIS Package to Create and Save Each Region Data to a different Flat File Destination from Sales Table in SQL Server
Scenario :
Suppose we have the Sales Data in the SQL Server Table "[Tbl_Sales]" as follows..
SELECT [Order_Id]
      ,[Sales_Region]
      ,[Month]
      ,[Quarter]
      ,[Cust_Id]
      ,[Prod_Id]
      ,[Net_Sales]

  FROM [dbo].[Tbl_Sales]
GO


Now we want to Save each Sales_Region (Eg: India, America,Japan..) Data to a different Flat File destination. This File should Save as with that Region Name with Date Time Suffix as follows...
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

Eg : SalesByRegion_America_20171118_105454.txt


Now lets Design the Package as follows...
------------------------------------------------------------------------------------------------------
Phase - 1 : Create the required Variables :
------------------------------------------------------------------------------------------------------
Here we create the below variables which we further use in the Package...
vDistRegionNamesObj : This is Dynamic System.Object type variable used to Store the SQL Full Result Set of the distinct Region names in run time, by Execute SQL Task.



vRegionName : This is a Dynamic String variable where we store the each Region Name in run time by ForEach Loop Container.


vOutputFolder : In this Static Variable we store the Output Folder Path where we save the each Region output data in text files..
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

vOutputFileName : In this Static variable we store the File Naming convention "SalesByRegion", which we use it final Output file Naming .

vDateTimeSuffix : This Variable is used to store the Current Date Tim suffix which we use in final Output file naming. The value for this Variable will calculate using the below expression..
REPLACE(REPLACE( REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,19),"-",""),":","")," ","_")

Eg : 20171118_114209

vOutputFileFullName : This is a very important Dynamic Variable used to store the final Output File Name. This Variable will also use as a Connecting String to the Flat File Destination.

Example Value : 
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\
SalesByRegion_America_20171118_105454.txt




------------------------------------------------------------------------------------------------------
Phase - 2 : Add the Execute SQL Task to Control Flow and Set the required Properties:

------------------------------------------------------------------------------------------------------


A) Add the Execute SQL Task to Control Flow



B) In Execute SQL Task Editor > General Tab , Set the Properties
Here I set the Properties as follows...

Connection = db_SalesDB  ; It is the OLED Connection to the Source Sales Database.
SQL StatementSelect Distinct Sales_Region from dbo.[Tbl_Sales] ; This will extract the Distinct Sales_Region names from the source table.

ResultSet= Full Result Set

Here, Full Result Set will allows you to read the Distinct Region Names extracted in the above SQL Statement.


Notes :
ResultSet = Full Result Set : This option we will use when we want to read Multiple Rows with a Single Column.
ResultSet = Single Row : This option we will use when we want to read Single Row with Multiple Columns.

C) In Execute SQL Task Editor > Result Set Tab, Set the Properties
Here we map the Result Set to an Object Variable to store the SQL Statement Result as follows..

Result Name = 0 ( The Index of the Variable , here we used only one variable so its index position is "0" )
Variable Name = User::vDistiRegionNamesObj




------------------------------------------------------------------------------------------------------
Phase - 3 : Add the ForEach Loop Container and Connect to Execute SQL Task then Set the required Properties:

------------------------------------------------------------------------------------------------------

A) Add the ForEach Loop Container and Connect to Execute SQL Task


B) In ForEach Loop Editor > Collection Tab , Set the properties
Here we need to Set the properties as follows...
Enumerator = ForEach ADO Enumerator ; We chosen this to read the rows from the Result Set, Passed by the Execute SQL Task

ADO Object Source Variable= User::vDistiRegionNamesObj
Enermerator Mode = Rows in the First Table



C) In ForEach Loop Editor > Variable Mapping Tab , Set the properties
Variable = User::vRegionName ;
Here the each Region Name read from the ADO Object Source Variable will be stored in this Variable.
Index = 0



------------------------------------------------------------------------------------------------------
Phase - 4 : Add the DataFlow Task inside ForEach Loop Container then Design the Flow:

------------------------------------------------------------------------------------------------------
A) Add the DataFlow Task inside the ForEach Loop Container


B) Add the OLEDB Source Inside the Data Flow


C) In the OLEDB Source Editor>Connection Manager, Set the required Properties
Here, We need to connect to the OLED Connection , then Write the SQL Query with the Parameter ( ? ) in Where clause as follows..

SELECT [Order_Id]
      ,[Sales_Region]
      ,[Month]
      ,[Quarter]
      ,[Cust_Id]
      ,[Prod_Id]
      ,[Net_Sales]
  FROM [dbo].[Tbl_Sales]
  WHERE
[Sales_Region] = ?





D) In the OLEDB Source Editor>Connection Manager, Set the Parameters.. Mapping.


Parameters= Paramter0
Variables=User::vRegionName
Parameter Direction = Input


Next Veiw the Columns from Source are Loading are or not..


Finally say OK.


E) Add the Flat File Destination and Connect to OLD DB Source


F) From Flat File Destination Editor > New , Create a Connection to Flat File Output location
Here select the Flat File Format as Delimited, as we want to save the data in Flat File as Tab Delimited Format..


Next from Flat File Connection Manger Editor > General Tab , click "Browse".
Next select the Output Folder and give some name like "SalesByRegion" , this name will change in the Run Time. 


Next click Open that will Establish a connection to the Destination.


G) From Flat File Connection Manger Editor> Columns Tab, Select you Column Delimiter
Here by Default it will select the Column Delimiter as Comma{,}. I chosen here as Tab{t}.

Next from the Advanced Tab, you can Modify the Columns Names and Data Types if you want to display them in different way in Flat File Destination.
Finally say OK.

H) Next in Flat File Destination Editor > Mappings, Do the Column mapping between Source and Editor.




Now the Flat File Connection is Ready. This Connection String we need make dynamic in Next Step.

F) Next Make the Flat File Destination Connection String Dynamic.
Go to "SalesByRegion_FlatFileDest" > Properties > Connection String and assign the Variable @[User::vOutputFileFullName] , to Dynamically pass the Output Destination File name.



I) Finally Run the Package..

Control Flow :
 Data Flow :

------------------------------------------------------------------------------------------------------
Output :
Now the Output looks like as follows..

Now open any Region File, say France , we will see only that Region Data.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------


Friday, 17 November 2017

How to Extract File Names from a Folder and its Sub Folders and Save in SQL Server Table by using SSIS Package

SSIS Package to Extract File Names from a Folder and its Sub Folders and save in SQL Server Table
Scenario :

Suppose we have the Files in a Folder and its SubFolders as follows..
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\



Now we want to Load the Files from above Folder and its inside Sub Folders to the below SQL Table "dbo.FilesInfo"

We can accomplish Scenario, by Designing the Package as follows..
------------------------------------------------------------------------------------------------------
Phase - 1 : Create the required Variables :
------------------------------------------------------------------------------------------------------
Here we create the 2 Variables as follows..

MainFolder : This is a Static Variable, to which we will assign the Main Folder path, through which we need to loop through to read the Files. This variable we will use as Collection variable in the ForEach Loop Container.
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\

FileFullPath :  The value for this variable is Mapped by ForEach Loop container. In this variable the Full File Name with Path will be stored by ForEach Loop Container during the run time.

The Example value of this Variable : 
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\EmpSrc.txt


------------------------------------------------------------------------------------------------------
Phase - 2 : Add the ForEach Loop Container to Control Flow then Set Variables Mapping :
------------------------------------------------------------------------------------------------------
A) Add the ForEach Loop Container to Control Flow
B) Next in ForEach Loop Editor>Collection Tab, do the variable Mapping to the Directory from Expressions


C) Next select the Options for..
Files as *.* to read and retrieve all file types.
Retrieve  File Name as "Full Qualified" to retrieve the File Full name with Extension and Forder Path.
Tick Mark the "Traverse SubFolders" to Loop Through Sub Folders as well.




D) Next in ForEach Loop Editor>Variable Mappings Tab, map the "User::FileFullPath" to Store the File Full name with Path, which will assign during run time.


------------------------------------------------------------------------------------------------------
Phase - 3 : Add the Execute SQL Task inside the ForEach Loop Container and do the Parameter Mapping :
------------------------------------------------------------------------------------------------------
A) Add the Execute SQL Task inside the ForEach Loop Container



B) In Execute SQL Task > General Tab set the required Connection and SQL Statement

Here I use the below SQL Statement with a Parameter ?
Insert Into [dbo].[FilesInfo] ([FileFullPathName] ) Values (?)


C) In Execute SQL Task > Parameter Mapping do the required Mappings
Here we map the Parameter "User::FileFullPath"  to pass the values to the Field "[FileFullPathName]" used in the above Insert Statement.



D) Finally Save and Run the Package

------------------------------------------------------------------------------------------------------
Output :
Now the Output looks like as follows in SQL Table...
Select * From [FilesInfo]



If you want to Retrieve the Folder and File Names from the File Full Path Column, you can run the below SQL Statement...

SELECT [FileId]
      , Left([FileFullPathName],Len([FileFullPathName])-CharIndex('\',Reverse                                ([FileFullPathName]))+1) As [FolderName]
      , Right([FileFullPathName],CharIndex('\',Reverse([FileFullPathName]))-1) As [FileName]
      ,[FileFullPathName]
      ,[LoadDateTime]
  FROM [dbo].[FilesInfo]
GO

Now the Output looks as follows....


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts