Overview
In this section we will talk about creating a new SSIS package from the begining. We will also look into some important SSIS package properties.
Explanation
Step 1 - Launch SQL Server Business Intelligence Studio from the start program.
Step 2 - Go to File --> New and click on Project...
Step 3 - Select Integration Services Project from the New Project wizard. Provide a proper name ie. MyFirstSSISSolution and click on OK button.
Right click on the SSIS Package folder and select New SSIS Package. It will create a new SSIS Package.
Renaming the existing SSIS Package
You can rename an existing SSIS package by right click on it and select Rename
Transaction Option - It provides the Transaction features into the SSIS Package like as database transactions. The default value for the TransactionOption is Supported. Here the SSIS package will execute within the existing transaction(i.e. called from the other package) and will join the existing default transaction. Two other modes are available as Required and NotSupported. TransactionOption Required means that the package will always execute within the transaction, either joining any existing transaction or creating a new one. TransactionOption NotSupported means the package will never bother with the transaction feature.
Isolation Level - IsolationLevel property in SSIS works like the Transaction Isolation Level in database. Default value is Serializable which may not be required always. In Serializable Isolation level, read locks are held until a transaction will not be committed or rolls back and also No data can be inserted due to additional lock. Purpose of the Serializble Isolation level is to decreased the data concurrency but also keep in mind that it may not be required always.
Protection Level - The protection level in SSIS provides various options to apply encryption on sensitive information like database connection string. Default value is EncryptSensitiveWithUserKey which may not requrie always. Data base connection string with the default protection level will encrypt with the user key of person who created the package. When other user executes the same package it will fail due to decryption of the connection string. To avoid this consequence , use the protection level as DontSaveSensitive which means you are not keeping any sensitive information in your package. The main benefit to do this change is that the other user can also execute the package.
I hope the above article will help you to create a new SSIS package with some additional information.
Provide your suggestions through the comment box.
In this section we will talk about creating a new SSIS package from the begining. We will also look into some important SSIS package properties.
Explanation
Step 1 - Launch SQL Server Business Intelligence Studio from the start program.
Step 2 - Go to File --> New and click on Project...
Step 3 - Select Integration Services Project from the New Project wizard. Provide a proper name ie. MyFirstSSISSolution and click on OK button.
Step 4 - It will create a new SSIS solution with a default package called Package.dtsx.
Now your SSIS Package is ready for the design. From the tool box, You can drag and drop the required component into the control flow tab as well as in to the data flow tab.
SSIS package contains few tabs like
a. Control flow - It defines the work flow between tasks.
b. Data flow - It defines the flow of data from source to destination.
c. Event Handlers - It helps to implement the custom logging if required.
d. Package Explorer - It helps to check on hierarchical view of configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables.
Adding a new SSIS Package into the existing solution
You can create a new SSIS package into the existing solution. To do that -Right click on the SSIS Package folder and select New SSIS Package. It will create a new SSIS Package.
Renaming the existing SSIS Package
You can rename an existing SSIS package by right click on it and select Rename
Important Points
Other than the above explanation, SQL Server Integration Services (SSIS) has other wonderful features -Transaction Option - It provides the Transaction features into the SSIS Package like as database transactions. The default value for the TransactionOption is Supported. Here the SSIS package will execute within the existing transaction(i.e. called from the other package) and will join the existing default transaction. Two other modes are available as Required and NotSupported. TransactionOption Required means that the package will always execute within the transaction, either joining any existing transaction or creating a new one. TransactionOption NotSupported means the package will never bother with the transaction feature.
Isolation Level - IsolationLevel property in SSIS works like the Transaction Isolation Level in database. Default value is Serializable which may not be required always. In Serializable Isolation level, read locks are held until a transaction will not be committed or rolls back and also No data can be inserted due to additional lock. Purpose of the Serializble Isolation level is to decreased the data concurrency but also keep in mind that it may not be required always.
Protection Level - The protection level in SSIS provides various options to apply encryption on sensitive information like database connection string. Default value is EncryptSensitiveWithUserKey which may not requrie always. Data base connection string with the default protection level will encrypt with the user key of person who created the package. When other user executes the same package it will fail due to decryption of the connection string. To avoid this consequence , use the protection level as DontSaveSensitive which means you are not keeping any sensitive information in your package. The main benefit to do this change is that the other user can also execute the package.
I hope the above article will help you to create a new SSIS package with some additional information.
Provide your suggestions through the comment box.
0 Comments