By: Ray Barley
Overview
In 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.
Explanation
To 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.
