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!!!

How easy or difficult to write dynamic SQL ? TIP#110

This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation.

This seems very easy but we need to cast the  parameters in VARCHAR and sometimes we stuck in single code.

I am sure this happened with all of us. 

Let’s understand first a straight forward way which we (most of  us) are  using.

In example I am using person table of Adventureworks database and it is just a simple query  which provide person row according to primary key.

Now the above query is OK but it can be write in much better way with one of the SQL server in build stored procedure which is sp_executeSQL. This is one of the best way which have certain advantage which will discuss in next tip. Now see how we can write above query in much better way

DECLARE @BusinessEntityID   INT
DECLARE @DynamicSQL NVARCHAR(200)
DECLARE @Parameters NVARCHAR(100)

SET @BusinessEntityID = 1
SET @Parameters =N'@ParameterBusinessEntityID INT'

SET @DynamicSQL = N'SELECT * FROM [Person].[Person] WHERE BusinessEntityId = @ParameterBusinessEntityID '
EXECUTE sp_executesql @DynamicSQL, @Parameters,  @ParameterBusinessEntityID =@BusinessEntityID

If you see above query, you will find we are not using any type conversion the statement is clear.

This is one of the best way to write dynamic query which is not only increase your statement’s readability but also increase performance of your query with certain amount.

Which we will discuss in next tip.

I hope now you are eager to use this and replace all your old fashion dynamic queries.

Please do post your feedback.

Enjoy !!!

RJ !!!

An interesting setting for NULL but don’t use it for future TIP #109

As we discussed earlier in TIP#103 for NULL in which I shared that we have to take extra care for NULL.

Now in this tip I would like to share one of the interesting setting for NULL. Although it is just for knowledge but don’t use it because it is deprecated in future version and by default you this setting is always on.

Still you are thinking for which setting I am talking about.

So , I am talking about “CONCAT_NULL_YIELDS_NULL”  . Let’s understand it by following example . By Default CONCAT_NULL_YIELDS_NULL is ON which means if anything added to NULL will be null as discussed earlier in our tip #103

Now, see what happens when we do it OFF.

So, We clearly saw in above image  when we set the CONCATE_NULL_YIELDS_NULL property to OFF it dissolved the NULL property.

My take on this property is that we should avoid it don’t try to make it OFF explicitly because if we do this then we explicitly breaking some hidden business rules.

I hope you will like this post.

Enjoy !!!

RJ !!

A big issue when try to Alter user define table type structure TIP #108

 

I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57.

Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type of a particular column from TINYINT to SMALLINT.

Now this change was seems very simple you just need to change a column’s data type  but when you are going to do this, you will find this is not pretty straight forward (if the User define table type is referred in different tables).

If you go through standard steps you need to follow below steps (for  a column data type change)

1) Create a new User define table  type

2) Replaced old User define table  type with new user define table type in each stored procedures

3) Remove old User define table type.

And , I would like to say a big thanks  Mr.Norlado  who post an alternative on stackoverflow

below is the alternative steps

1) Rename the existing table type with following command

EXEC sys.sp_rename 'dbo.StudentTableType', 'zStudentTableType';

2) Create Table type with your  changes which you want

CREATE TYPE dbo.StudentTableType AS TABLE(
    StudentId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    ClassId SMALLINT -- changed from tinyInt to smallint
);
3. Update the reference in sql entities

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.StudentTableType', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

4. Now drop the renamed table type

DROP TYPE dbo.zStudentTableType;

This 4 steps helped me a lot.

I hope this may help you as well.

Enjoy!!!

RJ!!!

Merge statement- A simple way for DML TIP#105

When, I first saw this statement a Hindi proverb  come to my mind which is “1 teer 2 nishane” . The meaning of this proverb means a single bow which hit two different aim in one shot.

So let me explain “Merge” statement in more detail. With the help of this Merge statement we can do insert ,update and delete with a single statement.

Let’s understand this by an example. Suppose we have two tables Source and Target as shown in below figure

Now these tables will have some data so lets write query to insert some pre pop data

Now what we want follow things

1) if  employeeId of source and target tables are not matched and if employee name starts with “S” in the source table  then insert the value in target table.  (means insert Sunil & steve’s records from source to destination table)

2) If employeeId of source and target tables are matched then update the Employee name from source table to target table  (if any record matched then update the employee name which not the case with current data)

3) If employeeId of source and target tables are not matched and Employee name in target table starts with “S” then delete that row from target table. (So Sunidhi’s row will be deleted from target table)

Let’s write Merge Statement  for all the 3 above work.

 

Now you observed that there is one more statements which is written explicitly apart from the condition which is OUTPUT clause which helps us to determine what operation is performed.

When you run this you will get following output

Now see the actual data in both the table.

 

So, a simple merge statement is doing all 3 operations like update, insert ,delete.

I hope this tip will help you some where.

Enjoy !!

RJ!!!

A myth about view TIP #102

 

I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax

“CREATE VIEW

AS

QUERY “

Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource

 

CREATE VIEW StudentSourceView
AS
SELECT StudentId,
       FirstName,
       LastName,
       Course,
       Detail
FROM tblStudentSource
Go

Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added

So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure

As shown record 2004’s FirstValue is updated to value “Updated”

 

Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!

 

Thanks

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!!!

How to send E-mail/E-mail with Attachments in SQL Server TIP #97

 

In last post, TIP#96 We have configured Database E-mail. Now in this post we will see how to send mail in SQL SERVER.

Sometimes we need to send mail for different requirements like

1) Notification mails like  data inserted /updated/deleted successfully

2)Send data reports like no of amount earned, failed transaction  etc.

for this SQL SERVER provides us stored procedure “sp_send_dbMail”.

The “SP_send_dbmail” has various parameters below are some important parameters like

@profile_Name : The profile which will use to send mail

@Recipients : To whom the mails need to be send

@body : This is message body

@Subject: Subject of the mail

@Query : SQL statement which you want to share

To send a simple E-mail  we can write following statements

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @body = 'Mail sent successfully.',
    @subject = 'Mail via SQL SERVER ' ;

We can also send a query result either inline text format or html format or as a  attachment

Below query will send mail to me with count of students of a class. the below mail is simple text format mail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ;

The above mail can also be send mail as a attachment with following command

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ,
    @attach_query_result_as_file = 1 ;

We can add @body_format = 'HTML' and use various HTML tag in query or body parameters

A part from this you can cross check E-mail status whether mail is sent or not if it is not sent the what is the reason.

Below are the statements which can help to cross check mail sent status

-- Show all the emails

SELECT * FROM msdb.dbo.sysmail_allitems

-- Show all the  sent mails
SELECT * FROM msdb.dbo.sysmail_sentitems

-- show all the un sent mails
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- show all the failed mail with reason
SELECT * FROM msdb.dbo.sysmail_faileditems

I hope this might help you somewhere.

Enjoy!!

RJ

How to Configure E-mail in SQL Server Step by Step–TIP #96

 

For different reason we need to send database report , data and other SQL Server database related stuff using E-mail . SQL  Server provides E-mail functionality using “Database Email” feature. We can setup any E-mail  in few simple steps.

Let’s follow below steps

Step 1:- Open “Management” option of object Explorer in SQL SERVER  management studio.

You will find “Database Mail” option. now click on configure Database mail option as shown below

 

Step 2:- When you click on Configure database mail you will get below wizard screen

Step 3:- Click on next button then you will below screen which is basically provide you option to setup or manage existing profile . In our case we are going to create a fresh database mail so let the first default option as is as shown in below figure and click on next button

Step 4:- You will get below screen. Just give a unique profile name and click on ADD button which is highlighted

Step 5: Now this is the main setting of mail “SMTP settings”

In this screen we have to give proper E-mail address, password , SMTP server name & Port number. In below screen I used gmail settings for a my gmail account. You can use any email address

Step 6:- Once you assure that everything is perfect in above screen then click on OK button. When click Ok button this SMTP profile will be saved and you can find in below screen now click on next button

Step 7:-  If you want you can make this profile as public default profile means default this profile will be use if someone wants to send mail from sql database. Now click Next button till the end of wizard you will following screens.

 

Step7:- Now click on Finish button then your setting will be actually saved in database and below screen will appear.

Now Once you close the above screen you are good to go.

You can test this by sending a test mail to your account using following screen

I hope this steps may help you to setup database email.

Enjoy Database E-mail and try to send a test mail to me also.

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 !!

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:-

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT 'Infinite Loop'
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!