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