Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data

Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.

Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax

After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.

Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.

We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

Enjoy !!!

RJ !!

Step by Step SSIS–Derived Column Transformation TIP # 120

Dear Friends,

In the series of Step by Step SSIS , Zero to Hero in SSIS this is another post in which we will try to understand what is Derived Column Transformation.

Let’s understand this by a real world example. Suppose, we are a shop keeper and our work is buy things from carpenter and sell them by adding our 20%  margin.

So , carpenters give us source data (file, sql server etc) with their rate according to product. What we do actually ,we add another column in our register by adding 20% which is our selling price. So ,our selling price column is a derived column here which we derived from source by adding some of our operations.

Isn’t it simple ?

Let’s understand this now step by step. I am using Adventureworks2012 database here as a source database and using product & ProductCategory table combination query. So , Assume this source data is data of carpenter’s products.  Here if you see below image we have productId, Product Name, color,Category & ListPrice (price at which we (Shop Keeper bought the the product)

Step 1:- It is useless to say here add a new Package in your project. Drag drop data flow task on canvas. Now double click the data flow task you will get a new screen where you need to drag drop source Assistance.

Now,need to configure source assistance where database will be adveturework2012.  If you see below we have same columns which we shared earlier in figure

Step 2:- I am sure you will not face any problem in configuration of source control. Now in step 2 we have to drag drop Derived column.  Once you drag drop derived column you need to provide output of source assistance to Derived Column Transformation. Now to configure this use context menu by right clicking and choose Edit option you will get below screen. Here we have different functions and operators which we can be utilized with columns and variables and parameters to create derived column.

If you see above highlighted row in image we can add a new column or can replace any existing column as shown in drop down.

Step 3:- If you see there is expression column also where we can add custom expression. Here we are multiplying 0.20 in list price and adding it again in Listprice and aliasing this new column SellingRate as shown in below figure

if you see below screenshot we can add other functions ,operators also.

Step 4:- I hope above steps are pretty much clear. Now add a destination file in which we will get derived column “SellingRate” . We have to configured the derived column. As shown in below figure we configured derived column.

Step 5:- Now, Once the destination file is configured run the package by pressing F5 or clicking run option. If everything working fine then we will get all the green checkbox sign as shown below figure

I hope this post might help you to understand the derived column.

Enjoy !!!

RJ!!!

Step by Step SSIS–Union ALL Tip # 119

 

Dear All,

In the series of  zero to hero SSIS series this is one of the simplest transformation control which known as UNION ALL. 

As you might aware UNION in general term  which means collective. In SSIS UNION all control is doing the same task.

It collect all the inputs may be of same type of sources or different type of sources and  union them all and provides single output for the same.

Lets understand this by below example.

Suppose, We have different text files which contain fruits &  vegetables name. Now our aim is to combine all the fruits and vegetables name which exists in these files.

if you see below image you will find 3 files which FruitA (Contains fruit names start with letter A) , FruitB (Contain fruit names start with  letter B), FruitP (contains fruit name start with letter P)

Now we need to combine this file using UNION ALL transformation. So follow below step by step

1) Step 1:- Step 1 is simplest step and now you all well versed in this. We need to add a new SSIS package file and drag drop  data flow task .

2) Step 2:- Now add different flat file source for all the 3 files and configure all the 3 files  fruitA.txt, fruitB.txt , fruitP.txt .

3) Once we configured all the 3 flat file sources, We have to drag drop UNION ALL transformation control.Once we drag drop UNION ALL we can provide the output of all the 3 files as a input in UNION ALL control as shown in below  figure.

Step 4:- Now, we have to configure the UNION ALL control. To do this right click on UNION ALL control and click on EDIT option. Here we have to configure all the columns which we need to union.

Step 5:- Once the UNION ALL is configured our next step is to get the output of UNION ALL in a resultant file. for this we have to drag drop destination flat file control as shown in below figure.

Step 6:- Now we have to configure the flat file destination and save it to specific location. A part from this we have to provide input to flat file destination which will be output of UNION ALL control. As shown in below figure I am saving the file on same location and giving name finaloutput.txt

Step 7:- Once all the above step is processed by us the final step is to run the package for this just click on RUN icon or hit F5. If everything is working fine the we will get following  result with all the green right check images.

If you see above image we did union of all the 3 files and saved output in a single file.

Now, for learning purpose and to make the post simplest I use only same type source you can use different type of source and destination as per your need.

A part from this UNION ALL doesn’t remove duplicate so if there are duplicates in the files it will not remove.

I hope this article will help you somewhere.  Please provide your inputs.

Enjoy !!!

RJ!!!

Find all the dates in a date range ? TIP #100

 

It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)
DECLARE @SeedDate AS DATE
SET @SeedDate = '2005-07-01'
WHILE @SeedDate <= @EndDate
BEGIN
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
END
SELECT * FROM @tblDateRange

Now second and interesting approach

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)

;WITH DatesCTE
AS (
SELECT @StartDate AS SalesDate
UNION ALL
SELECT DATEADD(d,1, SalesDate) As salesDate
FROM DatesCTE
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)
SELECT * FROM DatesCTE

SELECT * FROM @tblDateRange

These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.

Thanks

RJ

Enjoy !!!

How to hide my SQL Server instance in network ? TIP #99

 

In TIP #70  we saw how to find all the running SQL SERVER instance in a network or a machine.

to revise see below image.

This tip is just opposite to tip #70 you don’t want that your co-worker see your SQL Server instance running on your machine machine. (There are several reason behind this   and security is one of the most valuable aspects)

To achieve this you just need to do a very simple setting. Just follow below steps

1) Open “SQL SERVER Configuration Manager”

2) Once the screen is open right click on the instance which you want to hide from network (under  SQL SERVER network  configuration ) as shown below

3) When you click on Properties menu you will get a new screen as shown below

You need to set the value of Hide Instance option to Yes.

4) Click on apply button and restart the services.

Great , We achieved it. Isn’t it simple ?

I appreciate your feedback.

Enjoy!!!

RJ!!!

Very useful SET XACT_ABORT TIP#94

 

SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.

Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.

Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.

Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be a cause of locking problem.

In such situation  XACT_ABORT option is helpful.

By default XACT_ABORT option is off you can on it by following statement

   SET XACT_ABORT ON;

Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.

Let me explain with below example

Suppose I wrote following statement “Notice here XACT ABORT is OFF”

Now , when I run the above statements I will get following result in result window

And in the message window we will get following result

What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query

Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??

It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.

 

I appreciate your inputs on this valuable topic.

Thanks

RJ!!!

Data Compression–A unique feature PART–II TIP #92

 

In last post TIP #91  we talked about  What is Data compression ? What are the  features ?  Now in this tip we will take implement the data compression with basic steps.

So lets follow the steps

1) Right click on the table on which you need to implement compression. You will get following menu just select manage compression as shown in below figure

2) Once you click on the above option you will get following screen.

3) Now you can choose compression type from dropdown of the row either Page or Row . You can calculate estimated space saving by selecting the compression type. as shown in below figure

a) Page compression (In the last column you will find requested compressed space)

b) Row Compression (in the last column you will find request compression space)

Now once you selected appropriate compression type you can click on Next button of the wizard you will get below screen

Now you can generate the script for this compression type. We can directly run the script on database itself if we are sure which with compression type.

Once we run the above command on database we are good to go and our table is compressed.

I hope this steps will help you to compress our database tables.

Enjoy compression !!

Rj !!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which you want to encrypt’)

see below snap for more detail.

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ

How to insert value in Identity column ? TIP #80

 

Happy new year 2015 .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.

Change Data Capture (CDC)–An easy way to track data changes of a database–TIP #79

 

In last TIP #78  we have discussed Change tracker (CT) which was introduced in SQL SERVER 2008. CT feature only tracks which row is changes means on which row Insert/update/delete operation is performed but it does not track what exact value is changed.

If we want an audit of database means whatever changes occurred in database we want to capture those changes and later on see what are changes made then before CDC feature we have to right triggers but now in this case we can use Change Data capture (CDC) feature.

By the name it is clear that it capture the data which is changed.

Lets understand here step by step how to enable this feature

Step 1:- The first important step is to enable CDC on selected database. To enable CDC we have to write following command

EXEC sys.sp_cdc_enable_db

Note:- sometime you may get error while doing this so just cross check your database owner.

We can cross check whether CDC enable or not on database by following command

SELECT [name], database_id, is_cdc_enabled  FROM sys.databases  WHERE is_cdc_enabled = 1

Step 2:- Once we have enabled CDC setting on Database then we need to enable same setting for table which we need to capture.

For this we have to write following command

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='tblStudentCDC',
    @role_name ='StudentCDCRole',
    @supports_net_changes = 1

Now When we run above command, we will find following items added in our database. We will find new tables created automatically in our database under CDC schema as shown in below figure

Whenever we enable CDC for a table a new table is created in System tables (if already not created) under a new schema which is “CDC” which denote change data capture.

a) CDC.captured_Columns:- This table contains all the captured columns of a CDC enabled tables.

As shown in below figure:

SELECT * FROM [cdc].[Captured_Columns]

b) CDC.Change_tables:- By the name it is clear that this will table will contain list of all the tables on which we have enabled CDC feature.

SELECT * FROM [cdc].[change_tables]

c)CDC.Index_Columns:-  This table keeps information of all the Indexes of a table

SELECT * FROM [CDC].[Index_Columns]

d) [cdc].[ddl_history]:-  This table contains all the information of schema changes of a CDC enabled table.

SELECT * FROM [CDC].[ddl_History]

e)[CDC]. [lsn_time_mapping]:-  This table keeps all the LSN ( Log Sequence Number)related information. The base of this table is whatever the transaction done on the CDC enabled table that will be capture in this table.

SELECT * FROM [CDC].[lsn_time_mapping]

On the same time few jobs also created which you can find in SQL SERVER agent as shown in below figure

Step 3:-We can also cross check all CDC enabled tables by using following command

SELECT [name], is_tracked_by_cdc  FROM sys.tables  WHERE is_tracked_by_cdc = 1

Step 4:-  Every captured table has suffix _CT and  the name is same as enabled CDC table. In current scenario we have enabled CDC feature on tblStudentCDC so the capture table is tblStudentCDC_CT.

Now we can find all the data change reference of a CDC enabled table by following command.

SELECT * FROM [cdc].[dbo_tblStudentCDC_CT]

Sometimes it is also called mirror table which keeps all the track.We will find following extra columns

_$Start_lsn,_$end_lst_$sequal,_$Opeartion,_$updatemask

_$Operation column track all the operations on the table and the different values denotes  different operations status as mention below
Like if value is 1 then it is a Delete Statement (means record deleted from main table)
if value is 2 then it is Insert Statement (means record inserted in main table)
if value is 3 then it denotes Value before Update Statement
if value is 4 then it denotes Value after Update Statement

So, with this easy steps we can enable CDC on database and tables and can enjoy this feature.

We can easily disable this feature which we will discuss in the next post.

Till than enjoy CDC.

Enjoy !!!

RJ !!!