Is it possible to avoid XML configuration files and use package configuration in SSIS?

Question

Our team manages several versions of dtsconfig files, one for each release environment, and I'm trying to figure out if there is a way to avoid this and see if there is an easier way.

Our problem indicates where to find the input file. In each release environment, the input files are on a server that is different from the server that hosts the package file, which I suspect is the norm.

Ideally, I would like to specify the ConnectionString file, using an environment variable to indicate the location of the folder, but leaving part of the file name constant, as in the following example:

% FileFolder% \ MyFile.txt.

.. where% FileFolder% is \ OurServerName \ OurProjectName \ OurFilesFolder.

<?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="MyDomain\MyIDE" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="05/30/2009 01:26:00 PM" /> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[FLAT_FILE_PLAN].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>\\OurServerName\OurProjectName\OurFilesFolder\MyFile.txt</ConfiguredValue> </Configuration> </DTSConfiguration> 

Answer 1

Here is a possible solution that you can try to specify the path to the folder in the environment variable and use it in your package. This example does not use a configuration file ( .dtsconfig ), but it requires the installation of an Environment Variable named SSISFolderPath on the computer where the package is running.

Step by step:

• Go to Control Panel → System → Advanced system settings → click the Advanced tab → Click the Environment Variables... button → Click on the second New... at the bottom of the dialog box. These steps are valid for Windows Server 2008 or 2008 R2. Older Windows servers may have slightly different navigation options.

• Create an environment variable as shown in screenshot # 1 . I named it SSISFolderPath and gave the value c:\temp\SSISFolderPath\

• NOTE. . If you have a Visual Studio IDE open, close and reopen it so that the environment variable settings are visible both in the IDE and in the SSIS package.

• In the SSIS package, create a variable called FilePath , as shown in screenshot # 2 . Submit a link to learn how to create a variable in the SSIS package.

• In the SSIS package, select the SSIS menu and select Package Configurations... Before you do this, make sure you click on the package. Otherwise, the option will not be visible.

• Select the Enable package condifurations check box and click the Add... button. See screenshot # 3 .

• In the Select Configuration Type step, select the Environment variable value from the Configuration type drop-down list and select the newly created variable, here in this example it is SSISFolderPath from the Environment Variable drop-down list. Click Next > . See screenshot # 4 .

• In the Select Target Property step, expand the Variables section and expand the Properties FilePath variable and select Value in the Properties node section. Click Next > . See screenshot # 5 .

• In the Completing the Wizard step, specify the appropriate Configuration name . I gave Environment_Variable . Click the Finish button. See Screenshot # 6 .

• I placed the Script task on the Flow Control tab of the SSIS package to demonstrate that the variable is populated with the value from Environment Variables . Note that the variable is not currently relevant to the package. In the next step, when the Script task is called at run time, the variable will be populated with the value from the Environment Variables SSIFolderPath created on the computer and display it in the MessageBox. See Screenshot # 7 for sample output.

Hope this helps.

Script Task code: (Use the code below to replace the Main () method in the Script task)

VB Main () method code that can be used in SSIS 2005 and above

 Public Sub Main() Dim varCollection As Variables = Nothing Dts.VariableDispenser.LockForRead("User::FilePath") Dts.VariableDispenser.GetVariables(varCollection) MessageBox.Show(varCollection("User::FilePath").Value.ToString()) Dts.TaskResult = ScriptResults.Success End Sub 

C # Code of the Main () method, which can only be used in SSIS 2008 and above .

 public void Main() { Variables varCollection = null; Dts.VariableDispenser.LockForRead("User::FilePath"); Dts.VariableDispenser.GetVariables(ref varCollection); MessageBox.Show(varCollection["User::FilePath"].Value.ToString()); Dts.TaskResult = (int)ScriptResults.Success; } 

Screenshot # 1:

Screenshot # 2: Refer to this link to learn how to create a variable in the SSIS package.

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6:

Screenshot No. 7:

Screenshot No. 8: (This screenshot is only applicable for SSIS 2005)

Answer 2

@Siva has a very good answer, which is well suited for individual environments / developers on different computers, but in practice we found that if you run several environments (Dev / UAT / Prod / etc) on the same computer as using the environment variables are difficult because you need to constantly change the environment variable before starting subprocesses.

We tried several configuration options with different clients - only dtsConfig XML files that have the entire configuration inside (one for each environment) - an environment variable that controls which dtsConfig file is used - the minimum dtsConfig file that indicates the combination of SQL server / database data / table that has a complete configuration.

If we started from scratch, I would choose the third option now, since it was the most flexible for us.

Answer 3

I found that passing values ​​to variables from SQL Server Agent works well for me.

I run SSIS packages for scheduled tasks, so the SQL Server Job Type is the "SQL Server Integration Services Package", and then you can specify the values ​​on the Set Values ​​tab of the job properties.

When I run locally, for example, I have a path to the properties "\ Package.Variables [User :: ChildPackagePath] .Properties [Value]" with the value "D: \ Database \ ETL \ ETL \" (No quote on both). In different environments this is different.

It has pros and cons -

Con so that any user who has access to the tasks of the SQL Server agent can see the details (although only those people).

It is suggested that you can script the task and change any values ​​in the script to quickly create a new task in a new environment. You can also have different "versions" if you want: where the same packages are used by different tasks using different values.