Learning Practical SSIS

SSIS Exercise (Part-1)

With Mr. Joydeep Das

Help you to understand the power of Microsoft SSIS

Hello friends,

Here I decide to introduce a practical SSIS e-learning material for all of my friends who want to learn the Microsoft BI tools like SSIS.

This e-learning material is dedicated to the junior professions. This e-learning material contains the example only not any definition. This e-learning material is useful for those professional who already know SSIS but need practical example to understand it properly.

Hope you enjoy this and it is very much useful for your professional life. If you have you any query please feel free communicate with me.

With Regards Joydeep Das

das.joydeep2@gmail.com

http://www.sqlknowledgebank.blogspot.in

Exercise No Contents Name

1 Importing Data from a Test file to SQL Server Database 2 Exporting the SQL Server data to Excel sheet

3 Dataflow Error Handling

4 Setup Break point in SSIS Package 5 Setup the Check Points in SSIS Package

Importing Data from a Test file to SQL Server Database

Case Study

Please follow the mentioned steps to Import data from a Test file to SQL Server Database.

Step -1

The name of the text file is EmployeeRecord.txt

It contains columns like EMPID, EMPNAME, EMPGRADE

Step-2

Open the MS SQL Server BIDS by

Start  All Program  Microsoft SQL Server 2008  SQL Server Baseness Intelligent Development studio (BIDS)

The Objective of this exercise is to extract data form a Text file (with .TXT extension) and Load it to my MS SQL Server Table Objects.

Step-3

Now create a new Package with the name of SSIS Example.

Step-4

In the solution explorer of the BIDS, in the SSIS Package folder rename the Package.dtsx to TextFileImport.dtsx. Click ok on “Do you want to rename the package objects as well?” to Yes.

Step-5

In the control flow tab drag the OLEDB Flow task. Right click it and select the Edit from dropdown menu.

Step-6

In the data flow tab drag the flat file source from the data flow source of the tool bars. Right click it and select Edit.

Here I am choosing my EmployeeRecord.txt file and click the Open button. In the connection manager editor window we select 1 for Header row to skip. It help us to skip the columns name when importing data from my text file.

Select the Advance from the left-hand side and give the proper name of the columns and data type and size of the columns.

Now click OK button

Click on the Columns from left hand side of the Flat file source editor. From Available External columns we can de-select the columns that we do not want to appear or migrate.

Now click ok to finish it.

Step-7

Drag the OLEDB Destination from the Toolbars of Data Flow destination to Data Flow tab work area. Drag the data flow path (green color arrow) to OLEDB Destination.

In Configure OLEDB Connection manager select the New… button. In the connection manager window select the Native OLE DB \ SQL Server Native Clint 10.0 providers. Provide the SQL Server Instance name and choose the authentication mode. Then select the database name.

Click on the Test Connection button to check the database connectivity.

Click on the OK button.

Write down the CREATE TABLE statement on the Create table window. Here we give the table name is TBLEMP_RECORD.

CREATE TABLE [TBLEMP_RECORD] ( [EMPID] varchar(50),

[EMPFULLNAME] varchar(50), [EMPGRADE] varchar(50) )

Click OK on it.

In OLE DB Destination editor select the mappings from the right hand side and look at the mapping of the Available Input Columns and Available Destination Columns.

Click OK on it.

Step-8

Here 3 rows is moving from Flat file Source to OLE DB Destination.

Now open the SQL Server Management Studio and execute the following SQL Statement. SELECT * FROM TBLEMP_RECORD;

Exercise-2

Exporting the SQL Server data to Excel sheet

Here we must learn about the Import Export Wizard of SSIS. Here in this Example we are taking the Export Wizard.

Case Study

Step-1

From the BIDS open the SSIS project and then select the Project menu and then select SSIS Import and Export Wizard.

BIDS Open SSIS Project Project menu  SSIS Import and Export Wizard

Step-2

Welcome to SQL Server Import Export wizard windows appears.

The main objective is to Export the MS SQL Data from a Table objects to an Excel spread sheet.

Please read the instruction to know about it. Then click the Next button

In Choose a Data source window select the data source. In our case we are going to connect with MS SQL Server 2008 as our source database so we select “SQL Server Native Client 10.0”. Then provide the Server name. In our case the server name is “JOYDEEP-PC”. Then we have to choose the server authentication mode. We must select whether it is Windows

Authentication or SQL Server authentication. In case of SQL server Authentication we must provide the Username and password of SQL server. In our case we select the Use Windows Authentication and then click Next button.

In choose destination window we must provide the destination. In our case it is Microsoft Excel. Then select the Excel file path. In our case the name of the excel file it “Destination.xlsx” and the path is “F:\Practice_SQL\SSIS\SSIS Examples\Destination.xlsx”. Then we select the version of the Excel. In our case it is Microsoft Excel 2007.

Specify Table Copy or Query window we have two options. One is Copy data from one or more tables or views and second is Write a query to specify the data to transfer.

Step-6

Here we can choose one or more tables or views from source columns and in destination columns we must specify the name of Excel work book sheet name.

In our case we choose “MY_EMPDETAILS” as source table and in destination we provide “MY_EMPDETAILS” as the Excel work book sheet name.

If we choose our second options Write a query to specify the data to transfer we must write the SQL statement.

Click Next

In the Complete the Wizard window, check att the activity you done in this wizard and click on the Finish Button

Step-9

Observe the Excel file.

SELECT * FROM MY_EMPDETAILS

EMPID EMPNAME DEPT

1 JOYDEEP DAS Team Lead

2 SUDIP DAS Manager

3 TUHIN SHINAH Developer

4 SANGRAM JIT SR.Developer

5 DEBAYAN BISHWAS Developer

Step-10

Exercise - 3

Dataflow Error Handling

Case Study

Step-1

In the control flow tab drag a data flow tasks from the Control flow Item tool bar.

Double click it or Right click and Edit.

Step-2

In the data flow tab drag a Flat file source. Here we extract data form a faulty flat file which an gives us error.

Here we read a Text file as a data source and migrate the data to a MS SQL Server database Table Objects. If any error found to read the Text file it redirect the error records or data to a Text file destination with error description.

The faulty flat file structure is mentioned bellow

Please look here EMPID, instead of numerical 3 we use “THREE” and hence it generate error in case of data type mismatch.

Click the New Button. Click on Brows button to choose the Flat file form Flat file Connection manager window.

Select Advance from the right hand side section. Here we define the column name and data type of the columns and size of the columns.

Click OK button.

Step-4

Now we have to take the Data flow destination. Here we take two data flow destination. We use OLE DB Destination and connect the Flat File Source Output in it and for Flat file destination connect the Flat file source output. After that we have to configure the OLE DB destination and Flat file destination mentioned bellow.

Step-5

Select the OLE DB connection manager. In our case it is “JOYDEEP-PC.TEST_DB”. In the data base access mode select the Table or View and for name of the table or the view click on New button.

Then click OK button.

Step-6

Now go to the Mapping section of the flat file destination editor.

Step-7

Exercise - 4

Setup Break point in SSIS Package

Break point is nothing but a point where the developer can hold the execution to see how the code is executed at that point. In SSIS it needs to check the status of the task at the particular points.

Case Study

Step-1

To check this exercise we are taking the Example of Data flow error handling package tasks. In the control flow task hose the Data flow tasks right click it and select Edit Breakpoints…

Control flow tab Chose Data Flow Tasks  Right Click  From Shortcut menu Select Edit Breakpoints…

Here we see a simple example to set the Break point in SSIS control flow tasks and how to analyze it.

Here we must understand break Condition before enable it. There are ten events in the control flow described bellow (from book online)

 OnPreExecute: Called when a task is about to execute. This event is raised by a

task or a container immediately before it runs.

 OnPostExecute: Called immediately after the execution logic of the task finishes.

This event is raised by a task or container immediately after it runs.  OnError: Called by a task or container when an error occurs.

 OnWarning: Called when the task is in a state that does not justify an error, but

does warrant a warning.

 OnInformation: Called when the task is required to provide information.

 OnTaskFailed: Called by the task host when it fails.

 OnProgress: Called to update progress about task execution.

 OnQueryCancel: Called at any time in task processing when you can cancel

execution.

 OnVariableValueChanged: Called by the Integration Services runtime when the

Step-2

To understand it, now run the package.

Look at the red spot data flow tasks. Execution is hold for the break point and Data flow task is marked by yellow color.

Step-3

To get the information, first we start with call stack window to see whether the break point is located. To open the call stack window

Debug menu  Window  Call Stack

Or

Then Output Window (Ctrl + Alt + O) to determine what has been running at the package when the break occurs.

Finally the Locals Window (Ctrl + Alt + V, L) to know about the current status of the package execution.

Exercise - 5

Setup the Check Points in SSIS Package

Before starting this exercise let’s take a quick look about Check Point.

When we are working with a complicated and long running SSIS package and we want the ability to restart the package if it fails and have it starts at the point of failure. In others words if we don't want to execute the tasks that are already successful if the package restarted. This is done by establishing check points.

To understand it, let's assume that we have 10 tasks in the package. When the package is executed The Tasks -1 to Tasks -5 runs success fully at Tasks-6 it gives an error. When the package restarted for establishing check points it starts from Tasks -6 not from Tasks-1. To enabling restart ability within a package required first enabling a package to use check points, and second settings the specific tasks and containers to write checkpoints.

Case Study

Here we have a simple demonstration an Execute SQL Tasks Create a Table Objects and Three Other Execute SQL Tasks have INSERT Statements to Insert data on this table objects. Here

Step-1

Microsoft Distributed Transaction Coordinator (MSDTC) services are an important factor for paying transaction with SSIS package.

To start it

Control Panel  Administrative Tools  Services Console

Step-2

In control flow tab drag an Execute SQL tasks and then sequence container and drag three Execute SQL tasks in it.

Drag the precedence constraint of the Execute SQL task named Create Base to Sequence container.

Edit the Execute SQL Tasks (Create Base)

Set Connection property, here it is JOYDEEP-PC.TEST_DB. Click on SQLStatement and provide the following SQL CREATE table statement on it.

CREATE TABLE Tbl_STUDENT_DETAILS

(IDNO INT NOT NULL PRIMARY KEY,

STUDENTNAME VARCHAR(50) NOT NULL,

SUBJECT VARCHAR(50) NOT NULL)

Step-4

For Execute SQL Task -1

INSERT INTO Tbl_STUDENT_DETAILS

(IDNO, STUDENTNAME, [SUBJECT])

VALUES(1, 'JOYDEEP DAS', 'HINDI') For Execute SQL Task -2

INSERT INTO Tbl_STUDENT_DETAILS

(IDNO, STUDENTNAME, [SUBJECT])

VALUES('Two', 'SUKAMAL JANA', 'MATH')

For Execute SQL Task -3

Please look at Execute SQL Task -2 here an error exists in INSERT statement due to data type mismatch.

Step-5

Now set the Checkpoints property of the package.

Click Open button

Now set the Execute SQL Statement task FailedPackageOnFailure property to True.

Step-6

Now execute the package. Please look the Execute SQL Task – 2 Failed due to error in INSERT statement (Data type Mismatch).

Step-7

Now Fix the Error of SQL statement in Execute SQL Task -2 and run the package.