Saturday, August 29, 2015

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

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

Sql_server_data_tool

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

SSISCreate_Project_Indiandotnet

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.

DataFlowControl_indiandotnet_1

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.

Source_Assistance

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.

OtherSource

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.

SQL_Server_Connection

 

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.

OLEB_Data_Source

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.

Check_Uncheck_columns

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

SELECTED_Columns

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.

FileDestination

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.

DataFlow_arrow

 

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.

flatFileOption

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.

FlatFile_Detail

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.

Mapping

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.

RunPackage

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.

resultFlatFile

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

Monday, August 24, 2015

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

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.

SSIS_With_SQL_SERVER_2012

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

New_project_option

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

Ssis_templates

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. 

Basic_Concept

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.

ContainerTool

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.

DFT_tools

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.

Parameter_Add_screen

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.

Event_Handler

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.

DTSX_Package

 

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

Package

In Next post we will go one step ahead.

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

Enjoy !!!

RJ!!