Derived Column Transformation in SSIS is used to generate a new column by applying expression on input columns. An expression contains any combination of functions, variables, operators and Input Columns.
In one of our previous tutorial, Conditional Split in SSIS, we used an expression in condition where we identify the bank on the basis of first three characters of credit card column. In this post, we would use the same table and will generate a derived column Bank by applying condition and expression on Credit Card Column to identify the bank
Prerequisites
If you already have exercised any of the articles from this blog
You would already have Project SSIS-Tutorials and Credit Card Details Table in your database. You can skip first 2 steps and start with 3rd.
Step 1: Create Credit Card Details Table in SQL Server Database
Run SQL Server Management Studio, connect to database and run below script to create Credit Card Detail Table
Step 3: Add new package to the project. Name the package Derived-Column.
Step 4: Add Data Flow Task to Control Flow Tab
Double Click Data Flow Task to switch to Data Flow Task Tab.
Step 5: Add and Configure OLE DB Source
Step 6: Add and Configure Derived Column
Step 7: At this step we are done with adding and configuring Derived Column. Now instead of exporting data to some destination we can preview the data with derived column on the run-time.
We are done with creating the package. Let's execute the package preview the data.
Look at the output. The derived column Bank Name is generated in Data View Window.
In one of our previous tutorial, Conditional Split in SSIS, we used an expression in condition where we identify the bank on the basis of first three characters of credit card column. In this post, we would use the same table and will generate a derived column Bank by applying condition and expression on Credit Card Column to identify the bank
Prerequisites
- SQL Server with SSIS
- SQL Server Data Tools
- SQL Server Management Studio
If you already have exercised any of the articles from this blog
You would already have Project SSIS-Tutorials and Credit Card Details Table in your database. You can skip first 2 steps and start with 3rd.
Step 1: Create Credit Card Details Table in SQL Server Database
Run SQL Server Management Studio, connect to database and run below script to create Credit Card Detail Table
CREATE TABLE [dbo].[CreditCardDetails](
[CustomerId] [varchar](50) NULL,
[CreditCardNo] [varchar](50) NULL,
[TansactionType] [char](2) NULL,
[TransactionDate] [datetime] NULL,
[Amount] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CreditCardDetails] ([CustomerId], [CreditCardNo], [TansactionType], [TransactionDate], [Amount]) VALUES
('C00000001', 'SBI000000001', 'DR', '01/01/2016', 2500.00),
('C00000002', 'CAN000000001', 'DR', '01/01/2016', 2800.00),
('C00000001', 'SBI000000001', 'CR', '02/01/2016', 25.00),
('C00000003', 'SBI000000002', 'DR', '02/01/2016', 1485.00),
('C00000004', 'SBI000000003', 'DR', '03/01/2016', 2528.45),
('C00000002', 'CAN000000001', 'CR', '04/01/2016', 14.00),
('C00000003', 'SBI000000002', 'CR', '04/01/2016', 37.13),
('C00000004', 'SBI000000004', 'DR', '05/01/2016', 1000.00),
('C00000005', 'CAN000000002', 'DR', '05/01/2016', 3000.20)
Step 2: Run SQL Server Data Tools
Step 3: Add new package to the project. Name the package Derived-Column.
Step 4: Add Data Flow Task to Control Flow Tab
Step 5: Add and Configure OLE DB Source
- Drag and Drop OLE DB Source from SSIS Toolbox to Data Flow Task.
- Double Click OLE DB Source will open OLE DB Source Editor window.
- Select the Shared Data Connection if not selected we created in Step 2.
- Select Table CreditCardDetails.
Step 6: Add and Configure Derived Column
- Add Derived Column to Data Flow Task.
- Connect OLE DB Source to Derived Column.
- Double Click Derived Column to open Conditional Split Transformation Editor.
- Conditional Split Transformation Editor is divided into 3 sub windows.
- Columns, variables and parameters are used in expression to generate derived column(s).
- In-built functions are optionally used in expression like we are using LEFT function in the expression
- Derived Column: Here we configure derived column(s)
- Derived Column Name: Name of the derived column that would be generate. It is similar to Alias Column in T-SQL.
- Derived Column: You have two option here. Either generate column as a new column or replace the existing one.
- Expression: Here we write custom expression using columns,variable and in-built function to generate column
- Data Type: Data Type of the derivied column that would be generated
- Length: Display Length of the derivied column for non-mumeric columns
- Precision: Display precision if the data type of column is numeric
- Scale: Display scale if the columns is decimal/float.
- Let's add a derived column Bank Name as a new column, where we will identify bank on the basis of first three characters of Credit Card Column
Step 7: At this step we are done with adding and configuring Derived Column. Now instead of exporting data to some destination we can preview the data with derived column on the run-time.
- Add Conditional Split to the Data Flow Task
- Connect Derived Column to Conditional Split
- Right click Connector between Derived Column and Conditional Split and click Enable Data Viewer
We are done with creating the package. Let's execute the package preview the data.
Look at the output. The derived column Bank Name is generated in Data View Window.
No comments:
Write Comments