Easily configure SSIS packages in SQL2005

sql2005

In the previous DTS, there were many problems in the development, testing, and release migration of packages, and the typical problem was that you had to manually determine that all the connections in the package point to a physical server that actually existed.

Fortunately, the solution to this problem is now available in SSIS, which is package configuration
Package configuration is a mechanism for dynamically changing your SSIS objects and connection properties, storing these dynamically changing information outside of the package, and there are several ways to store the values and transfer them to the package:
XML file
Environment variables
Registry key value
Parent Package Variable

Here we will use an example to store these values using an XML file

In this example we will import a text file into the database table and dynamically configure the location source of the database

Start our package first:
  Suppose there is a text file PersonAge.txt that reads as follows:
  1,joe bloggs,27
  2,mary Smith , the
  3,fred jones,28
  destination table is dbo. Personage, use the following code to establish &NBSP
  create TABLE [dbo].[ Personage] (
   [personagekey] [int] null,
   [person] [varchar] () NULL,
   [personage] [int] NULL
  ) on [PRIMARY]
  go
  Start dragging component to control flow panel
   . An Execute SQL task component named truncate destination. Its SQL command is TRUNCATE TABLE dbo. Personage.
   . A data flow component named Import File.
   . Then associate the 2 with the
 
   
 
   start designing the Import File Data Flow task
    . A Flat file connection component points to the PersonAge.txt files
   . An OLE DB Connection component points to the presence of dbo. Personage the database of the table, and then configure the column's properties to match the table to the file
      
   
  &NBSP
   So far, the package has been successfully built

Set Package configuration
Now set the package configuration, which is very simple and has a good elasticity
Package configuration enables you to edit the properties of your package, variables, connections, and other attributes of your control flow, and note that you cannot edit the properties of your data flow component

On the menu bar, click DTS, Package configurations ... or right-click the control flow Design panel to select Package configurations ....
Then allow the package configuration selection in the package configuration and click Add
After clicking a welcome screen and letting you select the configuration type, the current selection of XML Configuration File
Then type the profile name, such as C:\PackageConfigurations\Environment.dtsConfig, and click Next
Then select the Connections.Destination.Properties initialcatalog and ServerName properties in the object tree browsing, and then click Next
Give your configuration a name, so the whole process is complete.

So your package will get a value of 2 properties during run time, you open the XML configuration file, and you'll see that you need to dynamically change the current value of the property

Now, you can easily migrate packages to another new environment, and you just need to change your package profile (C:\PackageConfigurations\Environment.dtsConfig) to