Asp.net & Sql server fundas with Rajat Jaiswal

How to Rebuild SQL Server Database After a Failure

2016-05-11T01:13:00.000-07:00

Overview

SQL Server plays an integral part of its users who creates their own database where they can store and retrieve data whenever required. Sometimes, the user may face issue while trying to access their database due to crash of SQL Server. To overcome such issue and to make the database accessible, we need to be able to make the database run on another physical server. For this, we can recreate the entire SQL Server environment on an existing server instead of building a new server and installing SQL again.

Things to keep in mind before Rebuild SQL Server Database

In order to recreate the SQL environment, we will use an existing SQL Server that is already setup.
Since the process involves restoring the master, msdb and user databases, we need to make sure that both the source and target SQL Servers must be on the exact service pack.
If the Source and Target environment are not similar, the restore of master database on the target server will not be possible.
To check the version of the target environment, use command ‘Select @@version’ in SSMS.
For version of Source environment, we can use a master backup file to check the SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild using the command ‘RESTORE HEADERONLY FROM DISK ='F:\Backup\master_backup_2015_10_9_000001_7422453.BAK'
Ensure that both the environments have the same version before beginning the rebuild process on the target server.
As an example for this page, we will use SQL 2008 SP2 (10.0.5000) for both source and destination.
In Source- master, model and msdb databases are located on drive D while tempdb is on a dedicated drive E with initial size of 10GB.
In Target- server has only one drive C without enough space to create 10GB file for tempdb.

Rebuilding SQL Server Database after a Failure

Here are the steps that need to be followed for rebuilding SQL Server database after a Failure:

Shutdown the SQL Services like SQL Server, Agent, SSIS, etc. on the target SQL Server using SQL Server Configuration Manager.

Go to command window by typing cmd in the search box. Use the command
‘sqlservr -m’
Open a new command prompt to start SQLCMD and use the following command to restore the master database:

The instance of SQL Server, which was started in single user mode stops after the completion of master database restore. While trying to run ‘sqlservr -m’ again, SQL Server will not start as master database has the reference that model and msdb are on drive D and tempdb is on E drive. Since the target server does not contain Drives D and E, we will use the following command:
‘sqlservr –c –m –f –T3608’
Where -c >> Starting SQL not as a service
-f >> starting with minimum configuration
-T3608 >>trace flag 3608 will skip automatic recovery of all databases except master database
We can change the location of the model and tempdb by using below commands after starting SQLCMD. We can also resize tempdb as well.

We will stop the SQL Server from the CMD window by either closing it directly or using CTRL+BREAK. Restart the SQL Server with the help of SQL Server Configuration Manager.

We will run the following command either on SSMS or SQLCMD to restore msdb database

In the end, we will restore the user databases and start the SQL Server Agent.

Conclusion

In the blog, we have been discussing ways on how to rebuild SQL Server database after a failure using existing server instead of creating a new one. After restoring the master and msdb databases, we were able to recover all the system information such as logins, schedules, jobs etc. without the need to recreate them. This method saves time since we do not need to build the entire server and install SQL Server all over again. However, if this methods fails to rebuild SQL Server Database then you can also take the help of third party utility like SysTools SQL Recovery

MS Build 2016 Live Streaming

2016-03-29T08:55:00.003-07:00

Know How to Manage SQL Server Error Logs

2016-03-28T00:22:00.000-07:00

The SQL Server error log file contains an account of the entire information generated in SQL Server. It contains warnings, informational messages, and information about critical events that take place in SQL Server. Furthermore, important information, like success and failure of logon events user-generated messages also get stored in the SQL Server error log. Since it is a valuable data point for the SQL Server administrators, managing the size of the error logs is the most important thing that needs to be done.

Every time, the SQL Server instance is started, the error log is initialized. In case the SQL Server instance has not been stated from a very long time, the error log can grow to a very large size. However, management of SQL Server error logs helps in maintaining the size of error log under control.

In this write-up, we will discuss some of these methods by which an administrator can manage SQL Server error logs effectively.

Methods for Managing SQL Server Error Logs

There are many methods, which can help the administrators to manage the SQL server error logs effectively. Some of them are:

Reinitialize SQL Server Error Logs

In order to reinitialize the server, the stored procedure sp_cycle_errorlog can be used. This stored procedure closes current error log and like a server restart, cycles error log extension. The new error log file will contain the version and copyright information and a line that will indicate that a new log is created.

Each time, SQL Server is started, the error log file is named to errorlog.1, and then errorlog.1 becomes errorlog.2 and so on. The syntax sp_cycle_errorlog cycles the error log files and that too without stopping and starting the server.

Increase Number Of Error Logs

The second method by which an administrator can effectually manage SQL Server error log files is by increasing the number of error log files for a specific instance. There are seven error logs by default in SQL Server, which are stored in the log folder. For better management of error logs, the user might want to create more than seven log files. This can be done by adding a registry key in the registry editor. Before making any changes in the registry, it is advised to create a backup of the entire data.

Browse to the following location in registry editor.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

Create a key with the name NumErrorLogs of value REG_DWORD type to it. Double click on it and change its value data to the number of extra error logs you require in SQL Server.

Limiting Error Log Size

Limiting the size of error log file is another method with the help of which the SQL Server error logs can be maintained. In SQL Server 2012 and subsequent versions, following mentioned script can be run to manage the size of an individual error log file.

Conclusion

With the help of the above-mentioned parameters, a database administrator can easily manage SQL Server the error log files.

Step by Step SSIS Multicast Transformation TIP #121

2015-10-18T10:56:00.000-07:00

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

2015-10-18T05:41:00.000-07:00

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

2015-10-17T05:24:00.000-07:00

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

Step by Step SSIS–Aggregate Transformation TIP #118

2015-10-02T02:27:00.000-07:00

Dear Friends,

In the series of step by step SSIS tutorial this is another post. In this post we will see Aggregate Transformation. I am pretty much sure you are aware of aggregation. Although , Just wanted to share that aggregation operation in generally memory expensive operation.

So, whenever you want aggregation & want to use group by function then in such situation you can use Aggregation transformation.

There are different group by option available like MIN, MAX , COUNT, SUM, AVERAGE etc.

Let’s understand how to use Aggregate transformation step by step.

For current example we are using Adventureworks database and we are using below query. Here we are fetching the product data with line total,unit price & other details.

if you see the records in the table you will find that there are multiple records for same product with different line total.

Our objective is to aggregate or do sum of Line total according to Line Number and export the result in a csv.

Step 1:- So , Now start with package creation add a new package in solution and drag drop source Assistant and configure the database connection as we did earlier in the tutorials

Step 1.1 – Drag drop Data flow task

Step 1.2 – double click data flow task and drag drop source assistance control

Step 1.3 – Configure source assistance

Step 2: Once the source assistance is configured with SQL SERVER connection string and specific query we will drag drop Aggregate Transformation control as shown in below figure

Step 3:- Now configure this aggregate control. So Just right click aggregate control and select Edit option. Now as we require Sum of LineTotal so we have selected SUM in operation column’s drop down and rest other has drop down option group by.

Step 4:-

Now, drag drop flat file destination and configure it.

Now configure flat file with mapping as shown in below figures

Step 5: Now run it you will get desire result as shown in below figure

If you see in the result we got 757 rows after processing 121,317

Now see the result in actual as shown in below file

I hope this example might help you to understand Aggregate transformation. Please provide your inputs.

Enjoy !!

RJ!!!

Step by Step SSIS - Conditional Split Transformation TIP #117

2015-09-21T01:53:00.000-07:00

In the series of Zero to Hero in SSIS this is our next post. In this post we will see Conditional Split transformation.

I am pretty much sure that by the name you got some impression what it would be.

So, a Conditional Split Transformation is a way by which you can conditionally split an input into multiple output.

Suppose, You are a food supplier and you cook both veg & non veg food. Now according to orders by client you need to move those order requests to particular kitchen to prepare those orders.

Let’s understand it by following step by step execution example.

In this example , I am using AdventureWorks database as a source database connection. We are fetching products along with categories. We need Bikes products in different file and rest other product in different file. You can see products with categories shown below in this image Accessories , bikes are the categories.

Step 1:- Create a new package and drag drop data flow task control and double click it. You will get a new screen which data flow task. Now drag drop Source Assistance. from SSIS controls tool bar.

Now as usual we have to configure the database connection string and set the database to adventureworks.

Step 2:- Once the database is configured right click on control and choose edit property. You will get below screen where you need to specify the query which we shown earlier in figure.

Once you done with above steps you need to configure columns as well so, click on Columns and configure it.

Step 3:- Now drag drop conditional split and connect input arrow to it as shown in figure

Step 4:- Now choose edit option by right clicking the conditional split and configure it. When you click you will get screen like below. You will find different condition operator. One important point to remember here you need to drag drop column name to condition textbox. As our aim is to have product with Bikes category in a different file and other categories in different file.So we use following condition as shown in below figure.

Step 5:-Now drag drop a flat file destination and assign the output of the above conditional split to File destination. You will get following screen. As you see we are assigning Bike condition output to this file which means all the products which belongs to bikes categories should be available in this.

Step 6: Now Configure the flat file destination by choosing the edit option.

Step 7:- In similar way we have to capture the output which not belongs to Bikes category as shown in below figure

Step 8: In nutshell we will get following screen

We almost done here. Now to check whether this conditional split is working or not. To check this hit F5 or run the package. So, if you see below everything working fine. We got 32 rows in bike category and 73 rows in other categories.

To cross check result we will see the file output as well

So , If you go through all the above steps we have achieved Conditional split example.

I hope this post might help you to understand conditional split.

In next step we will go for next step in SSIS.

Enjoy !!!

RJ!!!

Step by Step SSIS learning What Data Conversion use in SSIS ? TIP#116

2015-09-13T10:12:00.000-07:00

Dear Friends,

In the series of step by step learning of SSIS this is part #6 in which we are going to learn a new control Data Conversion. I am sure by the name it clear that this control will be use when we require data type conversion of input columns then we need this control. This is exactly same like CONVERT or CAST in our programming.

Although, Here it would be important to share SSIS also have data type which are similar to other programming or database’s data types

Like DT_NUMERIC is equivalent to numeric data type, DT_I2 is equivalent to smallint, DT_str is equivalent to VARCHAR and so on.

I am sure you will find this control fairly simple so without wasting much time lets start step by step.

For a change I am using excel data source here.

Suppose, we have an excel of sales order which have columns like sales order, subtotal,Tax amount, and freight.

Here sales order is varchar, while SubTotal , Tax amount & Freight are numeric with decimal 4 places. We want to convert the numeric field in integer and want result in a flat file. so let’s start.

Step1 :- Drag drop a Data flow Task control and double click it. Now add an excel data source from SSIS tool bar as shown in below figure.

Step 2:- Now next step is to configure this excel source. so to do this just right click and use EDIT option. You will get a screen where you need to provide the file which we want to access.

Once the connection with excel is established the Next very important step is to configure sheet as highlighted in below figure. remember you need to configure this sheet because an excel may have number of sheets available so you need to assign proper sheet whose data you want to process.

Step 3: Once the sheet is configured you can choose the columns as well which you want to process or want in destination by click column’s option in as shown in below figure

Step 4:- Now drag drop the data conversion control from SSIS toolbar

Step 5:- Now , open context menu and click edit button to configure data conversion. Now remember here we have to change data from numeric to integer (As discussed earlier our aim). so just change the desire data type which is integer (DT_I4) of all the three numeric data type. As shown in below figure. Also , point to remember here if when you do data type conversion you need to careful about Aliasing also. You will see copy of as a prefix on column.

Step 6:- Now, we need this output in flat file so adding a file destination control from SSIS toolbox and configuring it by clicking Edit button as shown in below figure.

Step 7:- In next step we have to configure the file location and columns which we require in the flat file. And delete unnecessary columns which are not required.

Step 8 : Once the above step is done, we are good to go to run this package. So,hold your breath for few second and hit F5 .

You will get find below screen. Which shows that all the provided rows are inserted in file.

Step 9 : To cross check whether file has integer columns or not lets open it.

So, if you see above screen we did it successfully. So we achieved our goal in this post and used DATA conversion control successfully.

I hope you feel this post useful.

Thanks for reading this post.

Enjoy !!!

How to use SORT control in SSIS ? TIP #115

2015-09-12T11:29:00.000-07:00

Dear Friends,

In the series of Learn SSIS step by step this is the 5th post. Now from this post we are going to use each transformation control one by one.

So, lets start with simplest one transformation control which is “Sort”.

By the name it is clear data it will sort the data which is provided to it and give sorted output as a result. Lets understand this by below step by step example. (Here I am not writing step to start visual studio and create a new project, I am pretty much sure you are aware of this now.)

Before going further let me tell you want we are going to do here. We will have a input result (which will be a text file) and then sort it and save in another file.

Step 1:-I renamed package to SortPackage if you want you can rename your package as well. Now drag drop a Data Flow task as shown below and double click DATA flow task

Step 2:- When you click it you will get a data flow screen where you can drag drop FLAT file source. You can choose source assistance as well. Now configure this flat file source. Means give the path of the file which you want to have as a input source. I am taking a text file which contain fruits name in different order. as shown in below figure

Right click flat file source click on EDIT option and follow the screens

Step 3:- Now once the file is configure. We have to drag drop SORT control from SSIS tool box as shown in below figure.

I also added the output of flat file source to sort control. As shown in above figure. Now configure it. Click on Sort control and you will get following screen. As show in below screen you can sort the data on any column and in any direction like Ascending or descending.

Right there is only one input column which is name so we are sorting name in Ascending order as shown below.

Step 4:- Now once this configuration is done we will save the data in new file with new name which will be sortedFruits. Now to achieve this drag drop the destination control. So I took same float file destination as shown in below figure

Step 5:- Now configure this file destination. Which means where we need to save this file and what are the columns which we required. so In current case there is only one column which is Name (Fruit name) and I am saving this file at same place with sortFruits name. so lets configure it by clicking right click on flat file destination and click on edit button

Step 6:- Once this is configured we need to run this package by pressing F5.When you run hit F5 and everything going right then in execution screen at each step you will find green right check image as shown in below screen

Step 7: Now to cross check we will first see whether that file is created or not. If created then whether we have sorted data or not. So lets open the flat files.

So, if you see at provided destination location a file is created and the data inside this file is sorted file.

I hope you like this first transformation control. till then Enjoy!!!

Thanks !!

RJ!!

How to deploy SSIS Package ? Step by Step SSIS TIP #114

2015-09-05T00:55:00.000-07:00

Dear Friends,
In last post we have successfully created our fist basic package. So, Now the next thing is how to deploy this package.

We have created this package for someone else or our client so we need to run this package on his /her machine.

For this we need to know how to deploy. Lets start this step by step

Step 1:- Open the existing solution and right click on the solution you will get following options as shown in figure. You need to click on “Convert to Package Deployment Model” as highlighted below

Step 2:- Once you click it you might get popup for confirmation just click OK.

Step 3: Now click on Properties you will get following screen in which you need to select the deployment option. And need to select true value for CreateDeploymentUtility as highlighted below in figure. Once You make this option true press OK or Apply Button.

Step 4:- Once you done with this again right click on the solution and again click on Covert to Package deployment model. Now this time you will get Wizard screen as shown below. Click on Next button

Step 5:- You will get below screen which is package selection screen. (Suppose you have multiple packages in a solution so for which package you want to create deployment you need to check / Uncheck according to your need.) Once you selected the Package click on Next button.

Step 6:- When you click next button you will get next option which is specific project properties as shown below in screen. In this we have an important option which is Protection level. It is use to set the level of showing sensitive data in package. for example a package may contain sql server connection string in which we have username and password and username and password are very important and sensitive information. Here ,with protection level we have option to encrypt this sensitive data or make package password protected. This will be an interesting topic which we will discuss separately in near future in separate post. for current time being just leave the default option as is and click on next button.

Step 7:- When you click on next button you will get following screen. This is the screen where you can call another package which this deployment. We will discuss it later for current deployment process we don’t want to call any other package. Just click on next button.

Step 8:- When you click on next button you will get following screen which says about configurations. We can create a configuration file which is just a simple XML file. If you are a .NET developer just assume it web.config file where we can keep connection string or other configurable item.

Step 9:- We can create parameters which can be use to pass the values in packages. In current solution we have not configured even parameters so don’t think to much about this currently. We will discuss it separately in coming post.

Step 10:- Now directly jump to Perform Conversion and skip review step. and click next you will get following screen of popup. Press OK and then try to rebuild the solution.

Step 11:- We did a build/rebuild to cross check package is correct. Now open the deployment folder in bin folder (You can find the address in step 3’s screen) . When you open the folder you will get two files one is Your package file with DTSX extension and another one which is called manifest file as shown in the figure below. Below ExportSQLToFlatFile is our manifest file and Package is our actual DTSX package file. (if we have config file then you will find config file as well)

Step 12:- Now, we successfully completed our first part of deployment which is basically creating a package deployment file which is a manifest file. We can copy these files on any server where we need to deploy the package. When you click on Manifest file which is ExportSQLToFlatFile you will get following screen

Step 13: Click on Next button when you get above screen you will get following screen which shows 2 options of package deployment

1) File System deployment 2) SQL Server deployment

Now for current demo we are using file system deployment and clicking next button.

Step 14:- When we click next button we get the screen which ask for folder where package will be deploy by default it is SQL SERVER’s DTS package folder so let it be currently and click Next button

Step 15:- After clicking next button , we will get following screen. Which is a confirmation screen for deployment.

Step 16:- Now the last step click the finish button and we are good to go.

So all the above steps are for creating a deployment for package and then deploy in SQL SERVER. Now , I am sure your next question will be what now ?

So, now we call this Package in SQL SERVER Job and configure job which execute this package time to time.

We, will discuss it more in detail coming post.

I hope you will like this post. please do post your inputs.

Thanks

Raj

How to create first basic package with SSIS ? tip #113

2015-08-29T02:58:00.000-07:00

Dear friends,

In last post #112 we understood WWH (What ,Why & How ) of SSIS. Now , lets move now real quick in practical session where we will try to create a basic simple package.

The example which we are creating is well known Export data from SQL SERVER to a flat file.

Step 1:- Open SQL SERVER Data Tool from start menu

Step 2:- Once it is open create a new project by clicking new project option. You have to select proper template as highlighted in below figure and give a name to project. As shown in below figure

Step 3:- Now drag drop data flow task control from SSIS toolbox. You can give customize message by click control’s text. I prefer this habit so down the line if after few month or years if you need to do some maintenance or logic change you don’t need to think a lot for why this control is for.

Step 4:- Now double click on Data flow control or click on data flow tab. Now on this area you have to drag drop source assistance. When you drag drop it you will get a pop as shown below.

The screen source assistance is the way by which we can select the data source on which we need to perform operation. As you are seeing in the image there are different data sources

Like SQL SERVER, Excel, Flat file, Oracle.

Although, you can select other sources also from SSIS toolbox as shown in below screen (As you are seeing there are various individual sources exists in toolbox itself so either use source assistance or drag drop individual source.It is worthless to explain here that excel source for excel file, flat file source for flat file and so on.

In this example we are selecting SQL SERVER. When you select Source Type then you have to configure connection Manager.For this we have to select “NEW” in connection manager panel and click OK button.

You will get below screen where you can give all the information related to SQL SERVER by which our package can connect with that data source. below I am using my SQL SERVER installed on my machine and using AdventureWorks database as shown in below image.

Now once connection is setup. Now we have to export a particular table data in a flat file. but you are wondering which table or data which we are going to export.

Step 5:- Now to select data which whether it is entire table, or stored procedure output , or view output or just simple SQL query. for this we need to double click on OLEDB data source and then we will get following screen.

Now ,here we can choose data access mode either table or view, or SQL command ,SQL command with variable. To make this first example easy we are choosing table or view and selecting “Product table “ in below drop down for Name of the table or the view.

Step 6:- Now once you have selected table or view you can select specific columns which we need to export in flat file. For this we have to select columns option available on left side. when you click it you will get below screen.

As shown in above figure you can check uncheck the columns which you need to export in flat file. we can rename the column name as well (as I did standard Cost to MRP). If you see below image

Here I am not explaining errorout option in detail in general sense just think it is configuration step if something failed.

Step 8:-

Now, we have source which we need to export in flat file, for this we may require a destination file in which we can store the data. So, Now we drag drop destination control which will be a flat file destination control as shown in below figure.

Step 9:- Now in above image you are seeing there are 2 arrows which is just flow direction means where the data needs to flow. Obviously in our case the data needs to flow from oledb source to flat file destination. So what we need to do drag the blue arrow and release it on flat file destination as shown below.

Step 10 :- I don’t know whether you noticed or not but let me tell you here. If you see above figure data is flowing from oledb source to flat file destination which is good but on same time there is cross image in red color which means there is some error in the control. So guess what is the error ?

I think you picked right the destination is not configured. So to do this we need to double click the flat file destination.

Step 11: When you double click you will get below screen. In which you need to configure the file location and file format like whether you want a delimiter file, fixed length file and many other option as shown in below figure. In our example we are using delimiter file option.

Step 12:- When you hit OK you will get following screen where you need to configure as shown in below figure. You need to give file location with file path. if you want different delimiter the you can choose that also.

Step 12:- Now press OK you will get flat file destination editor in which you can select mapping option and just check it for your query whether all the selected columns from source are aligning or not.

Step 12:- Once we done with this you will see the cross image in red disappear. If you are still seeing this it means there is something going wrong with configuration.

Now if everything is good then we can run our first own created package by pressing F5 or with Start option in IDE.

Step 13:- If everything is correct you will get right check in green apart from this you might be interested how many rows transfer from source to destination so that information also can be found. see below image for detail.

In our case we moved 504 rows. Now lets cross check at the destination location as well whether the file is created or not with these 504 rows.

WOW , we did it . We created our first simplest package which is export data from SQL to flat file.

I hope you enjoyed the learning. In next step we will do something more advance. mean while I request you all to do same practice and try to use excel instead of flat file.

Please do write your inputs. Let me know whether you are enjoying this series or not.

Enjoy !!!

RJ!!!

WWH of SSIS Zero to Hero in SSIS series (How to Use SSIS ?) TIP #112

2015-08-24T20:07:00.000-07:00

Hello Friends,

Welcome, back to Zero to Hero in SSIS series(Post #110). In last post #111 ,We gone through the WW (What & Why part) of SSIS in this post we will try to understand How to start SSIS ? How to use SSIS tool ?

You can start SSIS with SQL Server Data Tools which you can find in Microsoft SQL SERVER 2012 folder in start menu.

Here only the name is different but you will find same Visual Studio IDE when you click on this SQL SERVER data tools icon.

In the IDE when you click on New Project option from File Menu

You will get below screen in which you need to select Business Intelligence template and then select Integration Services. You will get two option “Integration Service Project” & Integration Service Import Project wizard

Now, We can select any template and proceed further. In general we select “Integration Service Project” . Here I am going to share some basic components when you try to create SSIS Project.

Don’t bother if you don’t understand the definition of explanation given below. I know theoretically it might be hard but practically it is much much easier.

1) Control Flow :- Control Flow is one of the most important component. Think this as as a container which helps in workflow. If you see below image Control Flow is first tab. It might contain tasks or container. It is helpful in sequencing of task (where task can for loop, send mail, xml process,etc.) Below is container tools which we can use.

2) Data Flow Task :- Another most important component is Data Flow Task. As the name state it is a task in which data flow. Isn’t it simple ? A data flow is part of Control Flow Task. All the major operation can be accomplished with the help of data flow task controls. When you use DFT(Data flow task) you will get various option like Data Sources (from where we need to fetch data) ,Transformation controls( Operation control like aggregation, split etc.) by which we can customize the data and last but not the least Destination in which format we need the data back like SQL Server, MYSQL, ORACLE etc. (We will discuss each DFT controls in detail in coming posts). Below is DFT tools which we will use later on.

3) Parameters:- I am sure you are aware of this parameters. Parameters are variables which help you in execution of your business logic (it might be possible you might require or not require.). Parameter has different scope and according to our need we will use and define the scope. not to worry about this as well right now. We will discuss and see practical use in coming posts. Below is the screen from which we can add parameters if required.

4) Event Handlers:- Event handler is the easy way to have control over your SSIS events. We can have different events like onError, onPostExectution etc. which give us liberty to improve the reliability ,monitoring and auditing of a package closely. We will surely going to do demo for this.

5) Package :- The final output of all the above core component is a Package. In other word Package is combination of various Control flow, Data flow tasks , parameters to achieve a ETL task. Earlier the extension of package was DTS and with latest version it is DTSX. If someone ask you what you do with SSIS tool you can simply say we create Packages in which we use different control flow & task flow control and once it is completed we deploy the Package. The important point here is we create a Package and then execute it by deploying.

Now in broader way if we envision it. Below picture might help you to understand it.

In Next post we will go one step ahead.

Please do provide your inputs what you are thinking so far ?

Enjoy !!!

RJ!!

WWH in SSIS ? Zero to Hero in SSIS series TIP#111

2015-08-22T02:20:00.000-07:00

Dear Friends,

As shared in last post Post #110, We are starting SSIS tutorial series ( A Step by Step SSIS learning tutorial) from today.

Before starting anything we might have some questions like What is SSIS ? Why SSIS ? & How to use SSIS ?

In general term I called this “WWH” What, why How ?

So, the straight forward answer is SSIS is SQL SERVER Integration Service (a Microsoft tool). It generally comes with SQL Server license. Although It is not always mandatory to have this SSIS tool with SQL Server license, You can install it standalone as well. SSIS is a ETL (Extract Transform loading) tool. We can can use SSIS with IDE which provided by Microsoft.

Now, I understand this is too straight forward and we are not able to digest easily. Let me explain it with an example.

I am leaving in Indore a city reside in Madhaya pradesh a state in the heart of India. Suppose the Chief Minister of MP allocated some funds to Municipal corporation of each cities like Indore, Ujjain, Bhopal etc. to do whatever best they can do with money and make their city a better place.

After few years The Chief Minister realized and thought let’s ask each cities how they utilized the money. What are the different areas on which they have work with the provided money.

The chief minister wants consolidate reports (which we generally called MIS reports) as well as some detailed reports.

Now, suppose different municipal corporations using different ways to restore data like Indore municipal corporation using Microsoft SQL SERVER, Bhopal municipal corporation using ORACLE, Ujjain’s municipal corporation using MYSQL, Devas municipal corporation using flat file system.

So there might be different heterogeneous systems used to maintain the data and data would be on different verticals like Transportation (Road & Bridges), Water harvesting (River & tanks) ,Agriculture , lighting etc.

To grab this entire data & consolidate them in one single database and providing the reports to Chief minister so that he can analyze which city doing progress , which city consuming money , how much work done in different vertical so that , he can take further decisions.

I hope you understand a big problem here which is gathering data from different heterogonous resource & compile them and provide a unique data base on which reports will create is a big task which can be easily done by an ETL (Extract Transform Loading) tool which is SQL SERVER Integration Service Tool.

Here Extract means extract or fetch data from different resource or data source whether it is SQL SERVER, ORACLE, MYSQL , Flat file or any other source

Transform means converting the data in to required single format

Load means prepare the data and provide final output.

Below is the image which illustrate same thing which we discussed earlier in this post.

So , I hope you understand the SSIS (ETL) role here.

Remember here SSIS use here to provide the data only rest work will be done by SSAS (SQL Server Analysis Services) and SSRS (SQL SERVER Reporting Services)

In this post we got 2 answers 1) What is SSIS ? 2) Why is SSIS? we will see How to use SSIS in next post.

Till than

Enjoy !!!

RJ !!!

Zero to hero in SSIS (SQL SERVER Integration Services) TIP #110

2015-08-17T21:12:00.000-07:00

Recently, Many friends of mine are interested in learning SSIS so , I thought to write some blogs which might help them.

I selected following area on which I will write blog on SSIS. It would be great if you also share your inputs.

Step 1: WWH of SSIS & Architecture of SSIS ? (What , Why, How )

Once you understand What, Why, How you are curious to know how to create a basic program /package in SSIS. I am sure you might have used this basic SSIS package but you might not aware (Import/Export) in SQL Sever.

Step 2:- How to create my first basic package using SSIS ?

With step 2 you will be more comfortable and confident that you can create package. Once your first package is created the next step which I think is deployment. How to deploy in real world and use it.

Step 3:- How to deploy SSIS package and different way of deploying ?

Step 4:- How to create basic data flow task package ?

With data flow task we will try to understand below common controls as well

4.a – Data Conversion

4.b - Conditional Split

4.c – Derived column

4.d – Lookup

4.e – Merge

4.f – Merge Join

4.g- Multi cast

4.h – Row Count

4.I – Sort

4.j – Union all

4.K – Others (remaining)

Step 5:- Understand For Loop Container

Step 6: Understand for each Loop container

Step 7: Sequence container

Step 8:- Bulk Insert Task

Step 9:- Script Task

Step 10:- Web service Task

Step 11: XML Task

Step 12:- File System Task

Step 13:- Execute Process task

Step 14:- WWH variables? How to define application variable ?

Step 15:- Performance improvement of SSIS Package

Step 16:- Interview questions related to SSIS

I hope with above blog steps those my friends will be benefited.

I appreciate your inputs as well what else we can include in this series .

Enjoy !!!

RJ !!!

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

2015-08-12T21:59:00.000-07:00

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

2015-08-09T20:53:00.000-07:00

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

2015-08-08T04:58:00.000-07:00

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

An interesting way of aliasing TIP#107

2015-08-04T18:29:00.000-07:00

I recently gone through something and found a unique way of aliasing. I thought it must be share so other techies also aware of it (or might be you already aware of it).

See below example

SELECT *
FROM (VALUES ('Rajat',30),
             ('Sandeep',40),
             ('Sunil',35),
             ('Shreya',50),
             ('Virendra',45)) AS T(Name,Runs)

Now when you it you will get following result

I hope you may like this tips.

Enjoy!!!

RJ!!!

A simple way for consistency TIP #106

2015-07-26T00:27:00.000-07:00

Whenever we develop application of maintain application we define sets of rules or policies like naming convention , data type, database & SQL SERVER properties (like which property should be unable or disable) but the problem is to cross check or to enforce these properties is very tedious.

To enforce the policies SQL SERVER provided a great feature which is Policy Based Management. This is the feature which helps you to not only write the policies or rules but also enforce and cross check whether those rules or policies is followed in your environment or not.

Although these feature is mainly for A DBA but I think it is good for a everyone who love SQL Server.

Now let me share an example which will help us to better understand Policy Based Management.

So to begin with firstly we have to open the Policy Based Management’s interface. Which we can get in Management folder in SQL SERVER’s object explore window. (You can see below yellow highlighted)

So when you expand the Policy Management in Management feature You will see 3 different folders which are facets , condition and policies.

So there are around 84 in build facets which is basically properties for different conditions.

Conditions are basically simple check applied using facets.

Policies are created on condition which will enforce once it created.

Below I am creating policy to check table who has row count =0 (Little bit odd) .You can try with stored procedure name not started with sp_ (will be good start as well).

So , Now create a simple condition by right click and choosing menu New condition

You will get following screen just add condition

We are creating a simple condition which will check the table which has RowCount is 0.

Once you created this condition you will get condition in conditions folder.

Now once condition is created we have to create policy. To create policy we have to right click on policy and select new policy.

When you click the new policy you will get following screen

Now we can add check Condition which we have created as shown in below figure. We can apply policy against target Like in below snap we want policy against every table for IndiandotnetDB

We can set evaluation mode according to our need.

Once you saved the policy we can run it any time.

We can evaluate policy by right click Evaluate as shown in below figure

When we run the policy we will get following result

So , If you see above highlighted row which means a table contain Row Count.

I hope with above steps you understand a basic way to create Policy Base Management concept.

Policy based management is surely a great tool to manage database.

Please feel free to give a chance whether you are DBA or not.

Enjoy PBM !!

Happy learning !!

Enjoy with new release of Visual Studio and SQL SERVER :)

2015-07-23T20:04:00.000-07:00

Dear All,

Although, I am bit late to share this information and might be possible most of you people aware of this.Recently Microsoft released Visual Studio 2015

and SQL SERVER 2016 CTP. Not needed to say that there are various awesome features in both build.

You can download latest Visual Studio 2015 from below link

https://www.visualstudio.com/downloads/download-visual-studio-vs

and SQL SERVER 2016 CTP from below link

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

In future post you will be find tips & tricks related to SQL SERVER 2016 & Visual Studio 2015.

Enjoy Learning !!!

Thanks

Merge statement- A simple way for DML TIP#105

2015-07-18T06:19:00.000-07:00

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

Proper Aliasing will help you somewhere TIP#104

2015-07-09T11:09:00.000-07:00

Recently, When we were delivering session on “SQL SERVER” one of the persons asked why we require Aliasing so I thought this might be question in everyone’s mind.

So lets start with couple of well know statements then we will see the actual problem where we need it explicitly.

There are two type of aliasing we can do a column aliasing & table aliasing.

Aliasing might help you in readability of stored procedure or function. It helps when you retrieving any calculated or derived column which need in your application so lets starts with some example.

if you see below example we are calculating a column but it does not have column name so fetch this specific value is very difficult so we require column Aliasing here

So as you saw above figure the calculated column is with name “No column name” we can not retrieve it easily so we require aliasing

Now ,lets talk about the table aliasing. So table aliasing help you in readability and it is helpful when you require self join or same table is required in join.

Lets see in below example we did a self join and we required aliasing

The aliasing will be helpful when same table join is required multiple times.

I hope this tip might helpful to someone.

I would appreciate if you provide you inputs as well where we can utilize aliasing.

Enjoy!!!

RJ!!!

It is OK its NULL only TIP #103

2015-07-06T20:37:00.000-07:00

We always take NULL very lightly. Like if we are designing database then whether it is necessary or not we allow the data field to accept null.We think it will not affect anything.

Actually , at some point this is not always true.

According to Microsoft NULL is an Unknown value. It is not EMPTY & ZERO. It is just unknown.

Now according to my experience NULL is very sensitive and should be handle with care.

While we are designing the database and in a particular table if a field can not be null according to business rule then please don’t allow NULL in that field for data consistency.

For example if we have a StudentEnrollment table and in the StudentEnrollment table we have reference of StudentId which is primary key of StudentId and that can not be NULL then please don’t make the field null able.

Let me share some more interesting facts

1) Two null value not equal :- Let me explain it with an example

Suppose I have tblStudentSource table which has different columns and a course column which have some null values as shown below

Now see below snap where we are comparing the null value course column. You will find in below snap that there is no result while we have null value in course

2) Use IS NULL or IS NOT NULL to handle null able column:- If a column is null and you want to show all the columns which are null then instead of comparing NULL value with column as shown in above figure use IS NULL as shown in below figure

In similar way if we want result which not have null course then we can write below query

3) Handle NULL carefully with IN clause

Let me explain with an example so we have 2 tables which are tblStudentSource (as shown in above (image 1) and another table which is tblCourse as shown in below figure

Now if you see we have course column in tblStudentSource table which has values similar to tblCourse’s course column and tblStudentSource’s course column contain some NULL values

Now suppose we wrote following statement and expecting that it will return all the course which are in tblCourse

Oh !! it is not returning any course. The reason behind it is NULL is not handled properly. Now to get desire result we have to write following statement

So , be always careful when your column has null values.

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!

Thanks

A myth about view TIP #102

2015-06-20T12:15:00.000-07:00

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

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