Session 6 : Deployment Of SSIS Package AnandSaturday, January 10, 2015 0 comments

Session 6 : Deployment Of SSIS Package

Step by Step SSIS Package Deployment

Step 1:
          Right click on the solution in solution explorer and click properties.In the left pane of Property window click Deployment Utility and in right pane set CreateDeploymentUtility property to True and click OK.

Step 2:

Right click on Solution in solution explorer and click Build.

Step 3:

Go to the Package path and then go to \bin\deployment folder.Double click the Integration Services Deployment Manifest file.

Step 4:
There are two types of Deployment File system deployment and Sql Server Deployment.As Sql server deployment is safe we can go with this.click next.Now specify the target machine server name and credentials and click browse and select the path.click next.

Step 5:

Now select the path in which the dependencies of package will install and then click next.In this window check the summary and click finish.Now you are done with your deployment.You can check by log in in target server Sql server Management studio.connect to integration services and under stored packages.You can find your deployed package.

Read More

Session 5 : How to Load Multiple Excel Sheets AnandThursday, January 8, 2015 0 comments

Session 5 : How to Load Multiple Excel Sheets


Source: Excel File have 3 sheets



Destination Table:

 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SampleTable](
 [Name] [nchar](10) NULL,
 [Value] [int] NULL
) ON [PRIMARY]

GO

1. Create a new SSIS project and solution. 
• Open SQL Server Business Intelligence Studio (BIDS).
• Select “New Project”
• Select ‘Integration Services’ template.
• Name of “SSIS” (this will be the name of the overall solution).
• Location of existing folder “C:\SSIS”.
• Check the ‘Create Directory for Solution’ box.
• Click ‘Ok’ and the Solution will be created.
• In the Solution Explorer window Right click the default package name of ‘Package.dtsx’ and select rename. 
• Use name “SampleExcel.dtsx” and press . Select yes to renaming the pack object as well.



2. Create Connection Managers
Excel Connection Manager
Right click on the connection manager pane and select “Excel Connection Manager”



OLE DB Connection Manager
Right click on the connection manager pane and select “OLE DB Connection Manager”



3. From the Toolbox, Double-click or drag the ‘Foreach Loop Container’ to the Control Flow canvas.
4. From the Toolbox, Double-click or drag the ‘Data Flow Task’ to the Foreach Loop Container.



5. Create a variable “SheetName” of string type and assign the value " Sheet1$". The variable name should be end with "$" to recognize the sheetname in excel connection manager



6. Edit the Foreach Loop Container, Go to the Collection tab and select the “Foreach ADO.NET Schema Rowset Enumerator” as enumerator



7. In Enumerator Configuration, Select connection drop down box, select new connection,
8. In popup window, Expand provider dropdown, and select "Microsoft Jet 4.0 OLE DB Provider"
9. Browse the file which has multiple Excel sheets from which we will load data.



10. Select the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".



11. In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container



12. Go to Variable Mapping and map the variable to Sheet Name and Set Index from 0 to 2.



13. Double-Click the Data Flow Task OR click the Data Flow tab to open the task.
14. From the Toolbox Double-click or drag the Excel Source to the Data Flow Task.
15. From the Toolbox Double-click or drag the OLE DB Destination to the Data Flow Task.
16. Double click the Excel Source, Expand Excel Connection Manager dropdown, and select Excel connection manager which is source excel connection
17. Expand the Data access mode , Select “Table Name or View name Variable”
18. Expand the Variable name. Select “User::SheetName”



19. Double click the OLD DB Destination, Select destination connection and destination table name. refer the below screen shot for details



20. In Mappings tab, Map the Source and destination columns shown in the image below



21. Run the package, it will load all the data from source Excel sheets to the destination SQL Table.
22. Data is successfully loaded to Destination Table from three sheets. Refer the below screen shot



Points to remember:
All Excel sheets in the source must have the same structure.

Read More

Session 5 : Check Points in SSIS AnandWednesday, January 7, 2015 0 comments

Session 5 : Check Points in SSIS

Introduction

In this article we will look into the Check point’s usage in SSIS package. Check points are nothing but a structure where we can restart the package at the point where it fails without having to restart from the first step. This feature is an added advantage for SSIS packaging which provides a better performance in order to achieve complex tasks. Check point saves the configuration details in a XML file which acts as the source for the later execution section. The package once restarted the default point is restored by the check points by referring to this xml file only.

Check point configuration is by default false in SSIS, we need to manually configure in order to use this feature. We will see here on how to configure and use the check points feature in SSIS packaging. Before enabling the check points we need to know the properties available with check points in order to use it effectively. It has 3 main properties as shown below

• CheckpointFileName – Automatically created XML file for configuration
• CheckpointUsage – Shows if the check point is in Use or not
• SaveCheckpoints – Shows if the check points saves or not in the packaging.

Let’s jump into the step by step process on how to configure check points and how to use it for our packages.

Steps :

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to configure Check points.

I have created a project here which has 2 tasks, both the tasks returns a positive response as success. At this point we will not see the properties and the tasks are executed perfectly as shown in the below screen

In order to enable the check points we will make the second task a negative task and try to run the project. It will display as shown in the below screen

No we can see the Check point properties in the property window of the package list as shown in the below screen. Here we have configured to save the check point and to use it.

Now the check points are configured and in order to use it now make the negative response to respond as positive and run the package again and see how it going to take it.

Conclusion

So in this section we have seen the usage of check points and how to make the configuration and how to use the check point as per the requirements.

Read More

Session 4 : Break Points in SSIS Anand 0 comments

Session 4 : Break Points in SSIS

Once the project is created, we will see on how to use breakpoint options available with SSIS.

I have created a project here which will copy the data from SQL db to Excel sheet from the Northwind database. I have created a Dataflow task along with OLEDB Source and Excel destination tasks to make the flow perfect. Now in order to activate the Break points just right click on the DataFlow task and select  “Edit Breakpoints” as shown in the below screen.

It will open a window which has the list of events available for the SSIS process execution. We need to select our exact needed break point to check the process at that particular point. There are about 10 events available and are as follows

• · OnPreExecute: Triggered when task is about to Execute
• · OnPostExecute: Triggered when task is executed
• · OnError: Triggered when error occurred with the Task
• · OnWarning: Triggered when task just throws a warning
• · OnInformation: Triggered when task is about to provide some information’s
• · OnTaskFailed: Triggered by task host when it fails to execute.
• · OnProgress: Triggered to update progress about task execution.
• · OnQueryCancel: Triggered in task processing when you can cancel execution.
• · OnVariableValueChanged: Triggered when the variable value is changed
• · OnCustomEvent: Triggered by tasks to raise custom task-defined events.

Here I have selected OnPostExecute event, so in my project once the task is execute this break point wil be triggered and we can check the execution process at that point.

Let’s now see on how the breakpoint execution works, if you notice after selecting the break point a red dot will appear in the task as break point notification as shown in the below screen

Now go ahead and press F5 to run the application. It will process the task and shows the execution after the tasks completed as shown in the below screen

In the above image if you see it points to the RED dot with an arrow symbol which indicates that the execution is waiting at this breakpoint to get completed. If you see the below pane in the IDE there are some windows which tells the execution process of this task.

The LOCALS windows at the bottom tells you exactly on the execution status if its success or failure, and the duration of the execution process and the execution status. Similar wise on the right hand side we can see the Breakpoints window which shows the complete list of breakpoints available not specific to the task but to the whole application.

OUTPUT window shows the execution process on the steps done and shows what is available at the current section. If we go to the data flow tab it shows the execution in green color which confirms that the execution is completed and then the process breakpoint triggered.

Conclusion

So in this section we have seen on the break point essentials in SSIS Packaging and the execution plan status available in order to check the process flow.

Read More

Session 3 : Import data using Wizard Anand 0 comments

Session 3 : Import data using Wizard

Introduction

In this article we will see on how to Import data from SQL server using the wizard which is provide with the SSMS (SQL server Management Studio). Using SSMS we can perform many tasks like copying data from one server to the other or from one data source to the other in variety of formats. Here our task is to do Import data from SQL server to Excel using the Wizard.

Steps:

Step 1: Go to Programs > Microsoft SQL Server 2008 > SQL Server Management Studio and connect to the list of server db’s which we have to perform the task as shown in the below screen

Step 2:  Once you locate the Database where we need to perform the transformation then Right click on the database and go to Tasks then select Import Data. It will open a welcome screen Click Next and move to the Datasource tab.

Step 3: Choose a Datasource tab helps to select the source of the data transformation selected at the initial stage itself.  Once the required information’s are selected click on next and it will ensure to select the destination source. Here we will do the transformation from Excel to SQL DB. So select Microsoft Excel from the drop down list as shown below.

Step 4: Now the destination data source window will open, here we need to specify the destination (In our example SQL Server DB) so select SQL Native Client from the drop down and Connections details to authenticate the connection as shown in the below screen

Step 5: Now we need to specify from which table we need to transform the data or we can write our own query based on which the data need to be transformed. Here we can select the table so mark that option and click on next as shown in the below screen

Step 6: Once we click on next button it will show the list of tables to be selected (from the excel sheet). Select the table which we need to do the transformation and click on preview to double check the output as shown in the below screen and click on Next button.

Step 7: Once we are done with the source and destination it will ask to save and execute the package. Click Next and Finish to complete the transformation as shown in the below screen.

Step 8: Since we are given the option to save the SSIS package it will ask for the configuration on which server we need to save the SSIS. Or you can give a path to save the SSIS as show in the below diagram

Step 9: Once we are done it will show the process on the how the task is carried over and shows the final result on the tasks completed as shown in the below screen. If it’s completed without any error it will Copy the data to the SQL DB table.

Conclusion

This article we have seen on how to use the Import Wizard to make a transformation and to execute the package using the wizard.

Read More

Session 2 :Export Data Using Wizard Anand 0 comments

Session 2 :Export Data Using Wizard

Introduction

In this article we will see on how to export the data from SQL server using the wizard which is provide with the SSMS (SQL server Management Studio). Using SSMS we can perform many tasks like copying data from one server to the other or from one data source to the other in variety of formats. Here our task is to do a transform of data from SQL server to Excel using the Wizard.

Steps

Step 1: Go to Programs à Microsoft SQL Server 2005 à SQL Server Management Studio and connect to the list of server db’s which we have to perform the task as shown in the below screen

Step 2:  Once you locate the Database where we need to perform the transformation then Right click on the database and go to Tasks then select Export Data. It will open a welcome screen Click Next and move to the Datasource tab.

Step 3: Choose a Datasource tab helps to select the source of the data transformation selected at the initial stage itself.  Once the required information’s are selected click on next and it will ensure to select the destination source.

Step 4: Now the destination data source window will open, here we need to specify the destination (In our example excel sheet) so select Microsoft Excel from the drop down and provide the path on where it should save the excel sheet

Step 5: Now we need to specify from which table we need to transform the data or we can write our own query based on which the data need to be transformed. Here we can select the table so mark that option and click on next as shown in the below screen

Step 6: Once we click on next button it will show the list of tables to be selected. Select the table which we need to do the transformation and click on preview to double check the output as shown in the below screen and click on Next button.

Step 7: Once we are done with the source and destination it will ask to save and execute the package. Click Next and Finish to complete the transformation as shown in the below screen.

Step 8: Once we are done it will show the process on the how the task is carried over and shows the final result on the tasks completed as shown in the below screen. If it’s completed without any error it will create the excel sheet at the folder where we specified in the destination tab

Conclusion

This article we have seen on how to use the export wizard to make a transformation and to execute the package using the wizard. Sample project is included along with the final result (excel sheet).

Read More