How to use SORT control in SSIS ? TIP #115

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

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