PACKAGE CONFIGURATION:
When you work in BIDS all values that you set up during development are stored as XML in the package's .dtsx file. The package is also stored in this file. Whenever a package is executed the first action a package takes is to look at its configurations and overwrite the packages current setting with the new settings from the configurations.
Package configurations are optional but they provide certain benefits like:
1.)Letting you update package settings without opening the package in BIDS.
2.)They also give a central location for settings that can be shared between package.
The common elements that are configured using the config file are:
Connection properties
Package Properties
Task and Container Properties
Package Variable Properties
Note:Data Flow properties cannot be configured by using package configurations. You can only configure the Data Flow Container itself.
ENABLING SSIS PACKAGE CONFIGURATION:
Open Package-->SSIS menu-->SSIS Configurations
Config Type:
Most commonly used configuration types are XML Configurations file and SQL Server configurations.
Creating an XML File Configurations:
There are 2 ways of creating an XML File
1.)Hardcoding the configuration file by specifying a name in the box provided.
2.)Use environment variable that uses the location in the file by creating a systems environment variable in the computer systems properties.
This is called indirect Configuration and is useful when the XML file location or file name might change.
Control Panel-->Systems and Security-->Advanced System Setting-->Advanced-->Environment Variables.
Creating an SQL Server Configurations:
Packages can share SQL Server configurations even if they are on different servers since the SQL Server configurations do not have to be stored in the local server.
Note: Which Config type to use:
1.)Use SQL Server config if there are multiple packages that need to share a config and the packages are executing on different computers.
2.) If packages are executed together. i.e. one package executes another which executes another one then use the parent package variable config.
3.) If planning to move packages from developmental to test server and then to production and each environment needs to use a different location then use the xml file
4.)If there are more than 1 config entries then that needs to put into a single SSIS configuration and either XML or SQL Server config file can be used.
Adding Properties to Your Configurations:
If entries already exists in the configuration file you selected SSIS prompts to reuse the setting or overwrite(which will clear the existing configurations) it.
If configuration entries do not exists or if overwrite is clicked you see the select properties to export page.
The Select Properties To Export Page lets you select the properties for the SSIS configurations that are selected. Properties are grouped by the following folder:
Variables
Connection Managers
Log Providers
Properties
Executables
Sharing, Ordering Configurations:
If there are several configurations the configurations are called in the order in which they are listed in the package configuration Organizer.
LIST OF PLACES THAT CAN USE EXPRESSIONS IN THE SSIS PACKAGE:
Derived Column Transformation
Conditional Split
For Loop
Variable Expression
Property Expression which include package properties at the control flow level or container and task properties accessed by editors and property window.
PROPERTY EXPRESSIONS:
It lets you update the properties of control flow during execution of package.
This can be done by first setting the property as an expression through the properties window and then set the expression property.
DEPLOYING A SSIS PACKAGE:
After the configurations and expressions are set in a package it is ready to move to a new environment. The deployment process requires 2 steps:
1.)Creating a package installer set by using package deployment utility
2.)Deploying the installer set to a new destination.
PACKAGE DEPLOYMENT:
When packages are moved from one environment to another there are 2 options for package storage.
SSIS Deployment to the File System:
Packages can be placed on file shares or file folders from which they could be executed.
SSIS Deployment to SQL Server:
Packages can be imported into SQL Servers from which they can be executed.
When you deploy a package to SQL Server the package is stored in a table named sysssispackages.
Deployment can be manual or driven through a utility.
1.)Manual package Deployment:
Connect to SSIS service and open the
2.)Deployment Utility:
It is the built in tool of SSIS and helps deploying an entire project at the same time to either a file system or SQL Server. It first generates a set of files that are packaged and ready to be deployed and then you run the package installer to run the deployment.
Creating a Installer Kit by Using a Deployment Utility:
Each SSIS Project has a property for enabling the deployment utility. When this property is enabled each time the project is built or debugged, the following items are copied to a single file location:
Packages
Misc files
Project Deployment Manifest(the installer kit config file)
To turn on the property follow the following steps:
1.)Right click SSIS project and click properties
2.)Change the createDeploymentUtility as True
3.)Set the location in which the deployment files will be stored.
4.)The AllowConfigChanges property is set to trueby default but it can be set to false if you need to limit the config changes while deployment.
5.)click OK
Next process is to run a build process on SSIS project to generate a deployment set. Right click on SSIS Project and create build.
Note: Turn off CreateDeploymentUtility when finished because any time any package in the project is debugge, a new deployment set will be created. This will overwrite the existing deployment sets so set the property is false after creating a deployment set.
Deploying Packages:
After the installer kit is created the next task is to take the set of files and deploy them to a destination by using Package installation Wizard. The following steps show how is it done:
1.)Move the installer kit to a destination computer. If the destination computer is on share the deployment files remain in place.
2.)Pick the file [ProjectName].SSISDeploymentManifest and open it. When you run the wizard you have the option to deploy the package to either a SQL Server db or a file system.
3.)On the Deploy SSIS package you select the destination as either a file system or SQL Server deployment.
4.)Based on what is selected you will be able to define the specific storage location.
5.) If the package includes config files it will now let you update and change the values of these configuration files.
Note: When deploying the package installation wizard copies the dependent files to the folder you specify.This includes XML Configuration files used by any package in the project(but only where XML config path is hard coded and does not use an environmental variable).Package installation files will update the XML config files in the package and point them to a new location.
Using SSIS DTUtil command line Utility:
SSIS comes with DTUtil which lets you perform package management tasks like deployment through command line or batch file processes.
The Package storage location references use /SQL, /File, /DTS parameters for sources and destinations.
Commands:
/COPY
/MOVE
/DELETE
/EXISTS
/ENCRYPT
/DECRYPT
/SIGN:Digitally signs a package
/FCREATE: Creates and manages SSIS Package store and in SQL Server MSDB db.
eg2: Below example shows copying a file from a local folder to the same location in the local folder with a new file name:
dtutil.exe /FILE c:\MyPackage.dtsx /COPY FILE; c:\MyCopiedPackage.dtsx
eg2: To delete the MyPackage.dtsx
dtutil.exe /SQL c:\MyPackage.dtsx /DELETE
eg3: To check for the existance of the file
dtutil.exe /SQL c:\MyPackage.dtsx /EXISTS
eg4: To deploy a package from file system to sql Server
dtutil.exe /FILE c:\MyCopiedPackage.dtsx /COPY SQL; \MyCopiedPackage.dtsx
When DTUtil is executed through an automated command line, use the follwing to interpret the results:
0 Success
1 Failure
4 Package not found
5 Package cannot load
6 Bad Syntax
No comments:
Post a Comment