Different ways to execute SSIS package

SSIS Package Execution

Execute SSIS package using BIDS

During the development phase of the project, developers can test the SSIS package execution by running the package from BIDS.

Open BIDS, configure a new package     ----------------->

Press Alt+Ctrl+L for solution explorer    ----------------->

Select the package    ----------------->

Right-click and select execute package option

Execute SSIS package using DTEXEC.EXE :

Command Line utility

Using the DTEXEC command-line utility can execute an SSIS package that is stored in the File system or SQL Server.

For example: DTEXEC.EXE /F “C:packagescheck points .dtsx”

Do you want to Master MSBI? Then enrol in "MSBI Training" This course will help you to master MSBI

Executes SSIS package using DTEXEC UI.EXE :

Using the execute package utility graphical interphase can execute an SSIS package that is stored in the file system or SQL server.

In the command line type DTEXEC UI.EXE and press enter which will open up execute package utility editor.

In executing package utility editor select the general tab,

Package Source – File System

Package – Click Browser    ----------------->

Select any package from the list and click open    ----------------->

Click execute to execute the linked/ embedded package.

(or)

The execute package utility is also used when you execute the SSIS package from the integration services node in the SQL server management studio.

Open SQL Server Management Studio    ----------------->

Connect to integration services    ----------------->

Expand stored packages    ----------------->

Expand file system

Select file system and right-click, Select import

Package editor

Package location – file system

Package – click browse    ----------------->

Select any package from

The list and click open    ----------------->

Package name - place cursor    ----------------->

Click ok    ----------------->

Select imported package    ----------------->

Right-click and select run package    ----------------->

In execute, package utility editor click execute

Execute the SSIS package using SQL server agent (using jobs):

Open SQL server management studio    ----------------->

Connect to database engine    ----------------->

Select SQL server agent

Note:  Ensure that SQL server agent service is start mode

Select Jobs    ----------------->

Right-click and select a new job

Provide Job Name as Load for each loop container    ----------------->

Select Steps page    ----------------->

Click new    ----------------->

Step Name - Load for each loop container

Type   - SQL Server Integration Service Package    ----------------->

In General Tab,

Package Source - File System

Package – click Browse    ----------------->

Select any package from the list of package    ----------------->

Click open    ----------------->

Click ok    ----------------->

Select Schedule package    ----------------->

Click new    ----------------->

Provide the job Name as Load For each loop –Container    ----------------->

Schedule Type – Recurring    ----------------->

Set the Frequency (When to start the execution of the specified package)    ----------------->

Click ok twice

Lookup Transformation:

It erased to compare the data from source to reference dataset. Using a Reference dataset using simple equally joint.

Note: While implementing data warehousing the reference data must be dimension table.

Steps to implement Look Up Transformation:

Open Business Intelligence development studio    ----------------->

Create a new package and rename it as lookup .dtsx     ----------------->

dn control flow drag and drop  and flow task    ----------------->

dn data flow, drag and drop  OLEDB Source    ----------------->

Double click on OLEDB Source to edit it     ----------------->

Provide Connection Manager if exists and select

Production .product category    ----------------->

Select columns and click ok

Drag and drop lookup Transformation and make

A connection from the source to lookup    ----------------->

Provide connection manager and select production

.product subcategory    ----------------->

Click configures error output and set Redirect Rows under error header    ----------------->

Click ok    ----------------->

Click ok    ----------------->

Drag and drop OLEDB destination      ----------------->

Make a connection from Lookup to destination    ----------------->

Double    click on OLEDB definition to configure it    ----------------->

Provide destination connection manager and    ----------------->

Click new to create a new   destination table    ----------------->

And rename the table as Matched data    ----------------->

Click ok    ----------------->

Select Mappings    ----------------->

Drag and drop OLEDB destination to capture

UN matched records from source to reference dataset.    ----------------->

Make a connection from Look Up to OLEDB destination

Using error output (Read data flow path)    ----------------->

Click ok in lookup error output editor    ----------------->

Double click on a destination to configure it    ----------------->

Provide destination connection Manager and click

New to create destination table and rename it as

UN matched-records    ----------------->

Select mapping and click ok    ----------------->

Execute package

Note:

• Matched records will be updated to the destination.
• All the unmatched records will be inserted to the destination.
• In Lookup transformation, edit or remove the mappings between all the columns expert between Product category ID.

Scenario: TO the Dynamic flat file destination

In control flow drag a drop data flow Task   ----------------->

Define the following variables with respect to the package.

Name	Data type	value
Uv source path	string	D:ssis packagespackages
Uv File Name	string	vendor

In Data flow drag and drop OLEDB source

Double click on OLEDB source to edit it   ----------------->

Provide a Source connection manager and select production. Production sub-category

From drop-down list         ----------------->

Select columns                          ----------------->

Drag and drop flat file destination and make a connection from OLEDB Source to flat-file       ----------------->

Double click on the flat file to edit it   ----------------->

Click new to configure new flat file connection Manager  ----------------->

Select Delimited flat-file format   ----------------->

Click ok  ----------------->

Connection Manager Name - Dynamic vendor flat file

Description – Dynamic vendor flat file

File Name – Type the following path and file with an extension that is not available at the destination.

D:ssis PackagePackagevendor.txt  ----------------->

Select columns page   ----------------->

Click ok  ----------------->

Select Mappings page   ----------------->

Click ok, In Connection    Manager, Select dynamic vendor flat file  ----------------->

Press F4 for properties  ----------------->

Expression – click browse

Select connection string and click in expression  builder, provide the following expression which create a new flat file dynamically

@ [user:: Uv source path]+””+@[user::UV File Path]  + “-”+(DT_WSTR,10)(DT_DBDATE)

@ [System::start Time] +’.txt’  ----------------->

Click ok twice  ----------------->

Close property window  ----------------->

Execute package

Script task: 

Script task is used to design custom interphases.

Scenario: 

Create a text file on every corresponding month dynamically using script task.

Define the following variables type string

Uv Source path: D:ssispackagespackages

Uv file name: Product category Details on

Uv Full path:  ----------------->

Drag and Drop script task  ----------------->

Double click on the script task to editor configure it.  ----------------->

Select Script from the left panel and set,  ----------------->

Read-Only Variables – Uv Source path, Uv File Name

Read-write variable – Uv Full path  ----------------->

Click Design Script, Opens MS Virtual Studio for Applications IDE,  ----------------->

Provide the following VB.Net Script to create dynamic text file the specified name.

Dim s Source path As String

Dim s File name As String

Dim s Full path As String

‘D:ssis package.Package

S Source path = Dts. variables (“Uv source path”).value.Tostring

‘Product category Details on sFile Name = Dts.variables(“Uv File Name”).Value.Tostring

‘D:ssis packagespackagesproduct category Details on-

sFull path = ssource path +  sFile Name +”_”+Month

(Now ()) ,Tostring() +Year(Now()).Tostring()+.”txt”

Dts.variables(“Uv Fullpath”).value = sFullpath.To string()  ----------------->

Select Debug Menu and Select build to build the above Scripting  ----------------->

Select file menu and Select close and return   ----------------->

Click ok     ----------------->

Drag and drop data flow task and make a connection from script task to data flow task   ----------------->

In Data Flow Drag and Drop OLEDB Source  ----------------->

Double click on OLEDB Source to configure to   ----------------->

Provide connection Manager if exits  ----------------->

Select Product category table  ----------------->

Select columns from left pane and click ok   ----------------->

Drag and drop flat file destination and make a connection from OLEDB source to flat file destination  ----------------->

Double click on flat file destination  ----------------->

Click new to create new connection Manager  ----------------->

Select Delimited flat file format  ----------------->

Click ok   ----------------->

Provide flat file connection manager name and description if any   ----------------->

Type the following path  ----------------->

D:ssis packagepackageproduct category Detailson.txt   ----------------->

Select columns from left panel  ----------------->

Click ok    ----------------->

Select mapping from left panel   ----------------->

Click ok    ----------------->

To connection manager select flat file connection manager   ----------------->

Press F4 for properties and set, expression - click Browse  ----------------->

Select connection string and click browse to build the expression in expression builder   ----------------->

Expand Variables  ----------------->

Drag and Drop User:: Uv Full path   in to expression  ----------------->

Section, i.e @ [user:: Uv Full path]      ----------------->

Click ok twice   ----------------->

Close properties window   ----------------->

Execute the package

Providing Security for SSIS Package:

The protection level is in the SSIS package that is used to specify how sensitive information is saved with

In the package and also whether to encrypt the package or sensitive portions of the package.

Example1 the sensitive information would be password to the Database.

Steps to Configure protection level in SSIS

Open Business Intelligence Development Studio

Create OLEDB connect with server authentication and provide

Design package

Select package in control flow, right-click

Select properties,

• Security

Protection level – Don’t save Sensitive

Don’t save Sensitive:

When you Specified Don’t Save Sensitive as the protection level, any sensitive information

Is not written to the package XML file when you save the package. This could be useful when

You want to make sure that anything sensitive is excluded from the package before sending

It to someone. After saving the package with this sending, open the OLEDB Connection Manager,

The Password is black even though save my password checkbox is checked.

Encrypt Sensitive With User Key:

Encrypt Sensitive with User Key encrypt Sensitive information based on the credentials of the

The user who created the Package.

There is a limitation with this setting if another user (a different user than the one who created

The package and saved it) open the package the following error will be displayed, error loading

Encrypts Sensitive with user key; failed to encrypt protection level XML load (dts. Password).

Encrypt Sensitive With Password:

The Encrypt Sensitive with password setting require a password in the package and that

Password will be used to encrypt and decrypt the sensitive information in the package. To fill in the

Package password clicks on the button in the package password field of the package and provide

Password and confirm password. When you open a package with this setting you will be prompted

To enter the password.

Note: The Encrypt Sensitive with Password Setting for the Production level property overcomes

The limitation of the encrypt Sensitive with user key setting by allowing any user to open the package

As long as they have to password.

Encrypt All With Password:

The Encrypt All with password Setting used to encrypt the entire content Of the SSIS package with the specified password. You specify the package Password in the Package

Property, Same as Encrypt Sensitive with password settings. After saving the package you can

View the package XML code that is already encrypted in between encrypted data tags in the package XML.

Encrypt All With User Key:

The   Encrypt All with User Key Setting is used to encrypt the entire

Contents of the SSIS package by using User Key this means that only the user who created the package

Will be able to open it, view or modify it, and run it.

Server Storage:

The server storage Settings for the Production level property allows the package

To return all Sensitive information when you are saving the package to the SQL server.  SSIS packages Saved to SQL Server use the MS DB Database.

Pivoting and Un pivoting:

The presentation of the data is required for easy analysis turning columns into rows

And rows into columns are another way of presentation of data. So that the end-user can understand

It easily.

Un pivot:

A process of turning columns to Rows is known as Unpivot.

Steps to Configure Unpivot

Prepare the following Excel Sheet

Year	Category	Jan	Feb	March	April
2008	Bikes	100	200	300	400
2008	Accessories	200	270	300	320
2009	Components	100	120	300	150
2009	Phones and components	400	800	400	300

MSBI Interview Questions

Open Business Intelligence Development Studio

Create a new package and rename it as UN pivot. dtsx

In control flow drag and drop Data Flow Task

In Data Flow drag and drop Excel source

Double click on Excel source to configure it    ----------------->

Click ok   ----------------->

Click Browse   ----------------->

Select Unpirot.xls file and click open   ----------------->

Click ok   ----------------->

Select sheet 1 from the drop-down list     ----------------->

Select columns and click ok   ----------------->

Drag and drop Unpivot transformation and make a connection from the excel source to Unpivot    ----------------->

Double click on Unpivot    ----------------->

Select the below columns to unpivot then, Jan, Feb, March, April, May, June    ----------------->

Rename pivot key value column Name as – Months    ----------------->

Specify sales amount as a Derived on Destination column for all the selected pivoted Key values or input columns    ----------------->

Click ok    ----------------->

Make sure that the Excel source file is closed.    ----------------->

Drag and drop Excel destination   ----------------->

Make a connection from Unpivot to Excel destination    ----------------->

Double click on Excel destination to configure it   ----------------->

Click New to create new destination excel sheet and rename it as Unpivot data    ----------------->

Click ok    ----------------->

Select Mappings     ----------------->

Execute package

Pivot:  

A   process of turning rows into columns is known as a pivot

 Steps to configure the pivot

Prepare the following Excel sheet for source data.

Year	Quarter	Sales Amount
2009	Q1	100
2009	Q2	200
2009	Q3	300
2009	Q4	400

 

Rename the Excel Pivot.xls    ----------------->

Open Business Intelligence Development Studio      ----------------->

Create a new package and rename it as Pivot. dtsx      ----------------->

In control flow drag and drop data flow task.     ----------------->

In Data Flow Drag and Drop Excel source    ----------------->

Double click on Excel source to configure it    ----------------->

Click New   ----------------->

Click Browse     ----------------->

Select pivot .xls and click open   ----------------->

Click ok    ----------------->

Select sheet 1 from the drop-down list    ----------------->

Drag and Drop pivot transformation and make a connection from Excel source to Pivot    ----------------->

Double click on Pivot      ----------------->

Select Input columns tab and check all input columns    ----------------->

Select input-output properties tab   ----------------->

Expand pivot default output    ----------------->

Expand Input column    ----------------->

Select Year and Set, pivot usage – 1     ----------------->

Select Quarter input column and Set, pivot usage – 2

Select Sales Amount input column and set, pivot usage – 3

Pivot Usage:

Pivot Usage tells ssis how to treat the data what its role during the

Transformation process

0 – The column is not pivoted1

• The column is part of the set key that identifiers 1 or more rows as part of 1 set.

All input rows with the same set key are considered into 1 output row.

• The column is a pivot column
• The values from this column are Placed in columns that are created as a result of the pivot.    ----------------->

Export pivot default output and create the following columns by click the add columns button Year Q1, Q2, Q3, Q4    ----------------->

Copy or Note the Lineage ID of input column year

Select year output column and set,

Pivot Key-value – Year

Source column – 58(Lineage ID of year input column)

Select Q1 and set,

Pivot Key-value – Q1

Source column – 64

Select Q2 and set,

Pivot Key value – Q2

Source column – 64

And follow the same process for Q3 and Q4 output columns    ----------------->

Click Refresh    ----------------->

Click ok   ----------------->

Drag and Drop Excel destination     ----------------->

Double click on Excel destination to edit it   ----------------->

Provide Excel connection Manager,   ----------------->

Click New to create a new table (sheet) and name it as pivot data     ----------------->

Click ok twice    ----------------->

Execute package

For In-depth knowledge of MSBI click on:

• SQL Server Analysis Services
• Report Server
• Transfer SQL server Objects Task
• Flat file source
• Slowly Changing Dimension