SSIS Package Creation and Deployment and Execution Method in SQL Server

Creating a Simple SSIS Package

Overview
In this section we will walk through creating a simple SSIS package from scratch.  As an example we will generate an Excel spreadsheet that can be used to manually enter a sales forecast by city and state.  The city and state information will be extracted from the AdventureWorksDW database and output to an Excel spreadsheet.  The sales department could use this spreadsheet to enter the forecast.  Another SSIS package will import the data from the spreadsheet and update a table in the AdventureWorksDW database.
In the sections that follow we will walk through the following steps:

• Create a new SSIS package and discuss some of the package properties
• Add Connection Managers for our data source and destination
• Add tasks to the package Control Flow
• Add tasks to the package Data Flow
• Execute the package in BIDS

Create a New SSIS Package

OverviewIn this section we will walk through creating a new SSIS package and adding it to an existing project.  We will also discuss a few of the important package properties.
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

You should see the following in the Solution Explorer:

To add a new SSIS package right click on the SSIS Packages node under the Tutorial-Sample-1 project and select New SSIS Package from the popup menu.  A new package will be created under the SSIS Packages node and will be named Package1.dtsx (or something similar).  Right click on Package1.dtsx, select Rename from the popup menu, and enter CreateSalesForecastInput as the new name for the package.
The designer will now display an empty Control Flow.  In a future section we will drag and drop tasks onto this design surface to generate our Excel spreadsheet.  For now click anywhere in the Control Flow designer to bring up the package properties window.

The package properties window contains many properties that can be set for the package.  The following is an example:

I collapsed the property groups and left expanded the few that we will discuss here:

• ProtectionLevel
• IsolationLevel
• TransactionOption

The ProtectionLevel provides for various options for encrypting the package and/or portions of the package.  I point this one out because the default value of EncryptSensitiveWithUserKey will often cause problems.  An example of sensitive information is a database connection string that contains a password in it.  The default setting will encrypt this information using the user key of the person who created the package.  When another user executes the package it will fail because that user's key will not be able to decrypt the connection string.  A good way around this is to change the ProtectionLevel to DontSaveSensitive, meaning you aren't going to put sensitive information in the package so there's no need to worry about encryption.
The IsolationLevel property is setting the Transaction Isolation Level.  Note that the default value is Serializable which may not be what you really need.  Serializable is the level where read locks are held until a transaction commits or rolls back which provides that no data read can be updated.  In addition range locks are held so that no data can be inserted such that rerunning any query in the transaction would return the rows added which weren't there at the beginning of the transaction.  Surely there are times when this transaction isolation level is warranted but certainly not always.   The Serializable level generally results in additional locking and decreased concurrency so you should consider whether you really need this and choose one of the other levels such as ReadCommitted.
The TransactionOption allows you to choose how the package behaves with respect to database transactions.  The default value of Supported is probably a good one.  If a package is executed within an existing transaction (i.e. called from another package) it will enlist or join the transaction which certainly makes sense as a default.  The other options are Required and NotSupported.  Required means that the package will always execute within a transaction, either joining the transaction of the caller or creating its own transaction.  NotSupported means the package does not join an existing transaction or create its own.

SQL Server Integration Services (SSIS) Connection Managers   (Connection Managers)

OverviewThis section is our second step in creating a simple SSIS package from scratch.  SSIS packages typically interact with a variety of data sources.  All of the most common data sources are supported right out of the box.  There is a Connection Managers area on the design surface where you can specify each data source that you will access.  In this section we will add two data sources to our package - one to access the AdventureWorksDW database and another to access our Excel spreadsheet.
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the Tutorial-Sample-1 project in Solution Explorer and you will see the following:

Double click on the SSIS package CreateSalesForecastInput.dtsx to open the package.  You should see the Connection Managers area of the designer in the middle of the screen near the bottom as shown below:

We are going to add a connection manager for the AdventureWorksDW database and another for the Excel spreadsheet that we will create and use as the sales forecast input.  To add the AdventureWorksDW connection manager simply right click inside the Connection Managers area then choose New OLEDB Connection from the popup menu.  The Configure OLEDB Connection Manager will be displayed; click the New button to display the Connection Manager dialog and fill it in as follows:

In my case the AdventureWorksDW database is on my local machine; change the Server name property as necessary for your environment.  If possible choose Use Windows Authentication to avoid having to specify a user id and password; this would be sensitive information that should be encrypted.  Click the Test Connection button to make sure you can connect to the database.  Click OK to complete this step.
To add a connection manager for our Excel spreadsheet, right click inside the Connection Managers area then choose New Connection from the popup menu, then select EXCEL from the Add SSIS Connection Manager dialog.  The Excel Connection Manager dialog will be displayed; enter a file name as shown below:

We are now finished adding the necessary Connection Managers to our package.

SQL Server Integration Services (SSIS) Control Flow   (Control Flow)

OverviewThis is our third step in creating a simple SSIS package from scratch.  In this section we will take a look at the Control Flow which contains the various tasks that the package will perform. 
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the Tutorial-Sample-1 project in Solution Explorer and you will see the following:

Double click on the SSIS package CreateSalesForecastInput.dtsx to open the package.  You should see the Toolbox on the left hand side of the screen as shown below:

As you can see there are quite a few built-in tasks available.  For our current package we only need a Data Flow task.  Drag and drop the Data Flow Task from the Toolbox onto the Control Flow designer.  After doing so the Control Flow designer should look like this:

Right click inside the Data Flow Task rectangle then select Rename from the popup menu.  Enter Create Sales Forecast Input Spreadsheet as the new name for the task. 
The designer includes a Data Flow tab which presents another design surface where you drag and drop the tasks that you want the data flow to perform.  We will do that in the next section.

SQL Server Integration Services (SSIS) Data Flow   (Data Flow)

OverviewThis is our fourth step in creating a simple SSIS package from scratch.  In this section we will take a look at the Data Flow designer which contains the various tasks that will be performed by a Data Flow task specified in the Control Flow. 
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the Tutorial-Sample-1 project in Solution Explorer and you will see the following:

Double click on the SSIS package CreateSalesForecastInput.dtsx to open the package.  Click on the Data Flow tab in the designer and you should see the empty Data Flow designer as shown below:

The Toolbox will now display the tasks that are available in the Data Flow as shown below:

As you can see there are quite a few built-in tasks available.  For our current package we want to retrieve some data from the AdventureWorksDW database and output it to an Excel spreadsheet.  To do this we need an OLE DB Source and an Excel Destination.  In addition we need to add a column to the Data Flow; this column will be the numeric column in the spreadsheet where the user enters the forecast amount.  Drag and drop an OLE DB Source, Derived Column, and Excel Destination from the Toolbox onto the Data Flow designer.  After doing so the Data Flow designer should look like this:

The red icons inside of the tasks are an indicator that there is some configuration required.  Right click on the OLE DB Source then select Edit from the popup menu.  This will open the OLE DB Source Editor on the Connection Manager page.  Fill in the dialog as shown below:

We setup the OLD DB connection manager in the Connection Managers step earlier.  The SQL command text contains a simple query to retrieve the list of cities and states in the United States.
Click on Columns to display the column mappings as shown below:

By default all columns returned from the query are selected and the Output Column names are the same.  The Output Column names can be edited; we will leave them as is.  Click OK to complete the OLE DB Source Editor.  You will now see a green and a red line dangling from the OLE DB Source task on the Data Flow designer.  The green line is the normal data flow; the red line is the error flow.  Drag the green line and connect it to the Derived Column task as shown below:

Notice that the red icon is now gone from the OLE DB Source task and it is now properly configured.
The Derived Column task allows us to add a column to the Data Flow.  Right click on it, select Edit, then fill in the Derived Column transformation Editor as shown below:

We have now added a new currency column to the data flow with a name of Forecast, and a value of 0.  Click on the Derived Column task and drag the green arrow to connect to the Excel Destination task.
Right click the Excel Destination task and select Edit from the popup menu.  The Excel Destination Editor dialog will be displayed.  Click the New button next to the Name of the Excel Sheet to display the Create Table dialog as shown below:

The Create Table dialog allows us to create a new table in the Excel spreadsheet.  The columns and their types are determined by the data flow.  We configured an OLE DB Source task that executes a query and a Derived Column task that added the Forecast column to the data flow.  You can edit the CREATE TABLE script if you like.  Click OK on the Create Table dialog and the Excel Destination Editor Connection Manager page will look like this:

We configured the Excel Connection Manager in step two of this tutorial.  Click on Mappings to display the Mappings page as shown below:

The above dialog sets the Destination Column names to the Input Column names by default.
At this point we have completed the Data Flow. Note that there are no longer any red icons in the task rectangles. We will proceed to the final step in this section of the tutorial and execute the package.

Executing a Package in BIDS

OverviewThis is our fifth and final step in creating a simple SSIS package from scratch.  In this section we will execute the package from within BIDS. 
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the Tutorial-Sample-1 project in Solution Explorer and you will see the following:

Right click on the SSIS package CreateSalesForecastInput.dtsx and select Execute Package from the popup menu.  While the package is running you will see each task turn green if it completes successfully or red if it fails.  Here is the Control Flow after the package has run successfully:

Here is the Data Flow after the package has run successfully:

After the package completes click Debug on the top level menu then select Stop Debugging.
We can open the Excel spreadsheet that the package created and see the following (only a portion of the spreadsheet is shown):

The above spreadsheet shows the columns from our query plus the Forecast column which we added with the Derived Column task.  This completes our tutorial section on creating a simple SSIS package.

Deploying SSIS Packages

OverviewWhen we are ready to deploy our packages we have the following options available:

• Deploy to the file system
• Deploy to the package store
• Deploy to SQL Server

The simplest approach to deployment is probably to deploy to the file system.  As SSIS package is actually just an XML file and it can simply be copied from its project location to a folder on the deployment target.  You can use the DOS COPY command, Windows Explorer, etc. to perform the copy operation.  The package store is a particular folder on the file system; the default for SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\DTS\Packages.  SSIS packages deployed to SQL Server are stored in the msdb database.
There are three ways to deploy our packages:

• Create a deployment utility from our project
• Use the DTUTIL command line tool
• Use SQL Server Management Studio (SSMS) 

SQL Server Integration Services (SSIS) Deployment Utility   (Deployment Utility)

OverviewThe deployment utility can be used to create an SSIS package installer.  The deployment utility is a built-in feature in an Integration Services project.  In this section we will review the steps to enable the deployment utility and create a deployment.
ExplanationTo begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Navigate to the Tutorial-Sample-1 project in Solution Explorer as shown below:

Right click on the Tutorial-Sample-1 project and select Properties from the popup menu.  Click Deployment Utility in the Configuration Properties list and fill in the dialog as follows:

Note that the CreateDeploymentUtility property is set to True; the default is False.  The DeploymentOutputPath specifies the location where the deployment files will be written.  The default is shown above and is relative to the project folder.  Click OK to save the settings.

Right click on the Tutorial-Sample-1 project in the Solution Explorer and select Build from the popup menu.  This will build the project and invoke the deployment utility.  If all of the SSIS packages are in a valid state, you will see the message Build Succeeded in the bottom left of the window.  Navigate to the bin\Deployment folder underneath the project folder to view the deployment files.  You will see the following files:

The above files represent the deployment.  You can copy them to the deployment target then double click on the Tutorial-Sample-1.SSISDeploymentManifest file to perform the deployment.

Command line deployment tool for SSIS packages   (DTUTIL)

OverviewSQL Server includes the command line tool DTUTIL.EXE which can be used to deploy SSIS packages.  DTUTIL is a good choice when you want to script out the deployment of SSIS packages.  DTUTIL can be executed from a Command Prompt or from a batch (.BAT) file.
ExplanationTo begin open a Command Prompt and navigate to the Tutorial-Sample-1 project folder as shown below:

In the examples that follow, I will show how to deploy the CreateSalesForecastInput.dtsx package to the file system, package store, and SQL Server.
To deploy to the file system, you could use the DOS COPY command, Windows Explorer, etc. or the following DTUTIL command (all on one line):

DTUTIL /FILE CreateSalesForecastInput.dtsx /COPY FILE;C:\temp\CreateSalesForecastInput.dtsx

Replace the path C:\temp as appropriate.
To deploy to the package store, type the following command (all on one line):

DTUTIL /FILE CreateSalesForecastInput.dtsx /COPY DTS;CreateSalesForecastInput

 To deploy to SQL Server, type the following command (all on one line):

DTUTIL /FILE CreateSalesForecastInput.dtsx /COPY SQL;CreateSalesForecastInput

The above command deploys to the default SQL Server instance on the local machine.  To deploy to a different SQL Server add the command line parameter /DESTSERVER "SERVERNAME\INSTANCENAME".

Deploying SSIS packages with SQL Server Management Studio

OverviewSQL Server Management Studio (SSMS) can be used to deploy SSIS packages to SQL Server or to the Package Store.
ExplanationTo begin launch SSMS and connect to Integration Services.  Note that the SQL Server Integration Services service must be running in order to do this.  You will see the following in the Object Explorer:

As you can see there are two nodes under Stored Packages: File System and MSDB.  File System is actually the package store with a default location in SQL Server 2005 of C:\Program Files\Microsoft SQL Server\90\DTS\Packages.  MSDB is of course the MSDB database.
In the examples that follow we will deploy the CreateSalesForecastInput.dtsx package from its location in the project folder to the package store and the MSDB database.
To deploy to the package store, right click on the File System node and select Import package from the popup menu.  Fill in the Import Package dialog as shown below:

Click OK to import the package.
To deploy to the the MSDB database, right click on the MSDB node and select Import package from the popup menu.  Fill in the Import Package dialog as shown below:

Deploying SSIS packages with SQL Server Management Studio

OverviewSQL Server Management Studio (SSMS) can be used to deploy SSIS packages to SQL Server or to the Package Store.
ExplanationTo begin launch SSMS and connect to Integration Services.  Note that the SQL Server Integration Services service must be running in order to do this.  You will see the following in the Object Explorer:

As you can see there are two nodes under Stored Packages: File System and MSDB.  File System is actually the package store with a default location in SQL Server 2005 of C:\Program Files\Microsoft SQL Server\90\DTS\Packages.  MSDB is of course the MSDB database.
In the examples that follow we will deploy the CreateSalesForecastInput.dtsx package from its location in the project folder to the package store and the MSDB database.
To deploy to the package store, right click on the File System node and select Import package from the popup menu.  Fill in the Import Package dialog as shown below:

Click OK to import the package.
To deploy to the the MSDB database, right click on the MSDB node and select Import package from the popup menu.  Fill in the Import Package dialog as shown below:

 

 

Executing SSIS Packages

OverviewWe have the following three options available to execute an SSIS package:

• DTEXEC command line utility
• DTEXECUI windows application
• SQL Server Agent

In this section of the tutorial we will discuss the execution options..

Command line tool to execute SSIS packages   (DTEXEC)

OverviewSQL Server includes the command line tool DTEXEC.EXE which can be used to execute an SSIS package.  DTEXEC can be run from a Command Prompt or from a batch (.BAT) file.
ExplanationTo begin open a Command Prompt and navigate to the Tutorial-Sample-1 project folder as shown below:

It is not necessary to run DTEXEC from the folder where the SSIS package is located; it's just easier to change to the directory for demonstration purposes.  Type the following command to execute the CreateSalesForecastInput.dtsx package:

DTEXEC /FILE CreateSalesForecastInput.dtsx

To see the complete list of command line options for DTEXEC type:

DTEXEC /? 

Windows application to execute SSIS packages   (DTEXECUI)

OverviewSQL Server includes the Windows application DTEXECUI.EXE which can be used to execute an SSIS package.  DTEXECUI  provides a graphical user interface that can be used to specify the various options to be set when executing an SSIS package.  You can launch DTEXECUI by double-clicking on an SSIS package file (.dtsx).  You can also launch DTEXECUI from a Command Prompt then specify the package to execute.
ExplanationTo begin open Windows Explorer (or My Computer) and navigate to the Tutorial-Sample-1 project folder.  Double-click on the CreateSalesForecastInput.dtsx SSIS package and you will see the following multi-page dialog displayed:

As you can see there are many settings available when you use this utility.  As a general rule you can simply click the Execute button to run your package.  You can also fine tune your execution by clicking through the various screens and entering your own settings.  After changing the settings click on Command Line which will show you the DTEXEC command line based on the settings you have chosen.

Scheduling SSIS packages with SQL Server Agent   (SQL Server Agent)

OverviewSQL Server Agent includes the SQL Server Integration Services Package job step type which allows you to execute an SSIS package in a SQL Server Agent job step. This can be especially handy as it allows you to schedule the execution of an SSIS package so that it runs without any user interaction.
ExplanationTo begin open SSMS, connect to the Database Engine, and drill down to the SQL Server Agent node in the Object Explorer.  Right click on the Jobs node and select New Job from the popup menu.  Go to the Steps page, click New, and fill in the dialog as shown below:

In the example above the SSIS package to be executed is deployed to SQL Server (i.e. the MSDB database).  You can also execute packages deployed to the file system or the SSIS package store.
Note that the Run as setting is the SQL Agent Service Account.  This is the default setting although from a security standpoint it may not be what you want.  You can setup a Proxy that allows you to give a particular credential permission to execute an SSIS package from a SQL Server Agent job step.
The first step to setting up the proxy is to create a credential (alternatively you could use an existing credential).  Navigate to Security then Credentials in SSMS Object Explorer and right click to create a new credential as shown below:

Navigate to SQL Server Agent then Proxies in SSMS Object Explorer and right click to create a new proxy as shown below:

You must specify the credential and check SQL Server Integration Services Package.  Now when you create or edit a SQL Server Agent job step, you can specify the proxy for the Run as setting as shown below:

Download SSIS Samples

Download the Samples for the SSIS Tutorial
Download the code samples in this tutorial