Is it possible to avoid XML config files and still

问题:

Our team manages multiple versions of dtsconfig files, one for each release environment, and I am trying to see if there is a way to avoid this and see if there is a simpler way.

Our issue is with specify where to find the input file. In each release environment, the input files is on a server that is different than the server which hosts the packages file, which I suspect is the norm.

Ideally, I would like to be able to specify a file ConnectionString using an Environmental variable to specify the folder location but leave the file name portion constant as in the following example:

%FileFolder%\MyFile.txt.

..where %FileFolder% is equal to \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>

回答1:

Here is a possible solution that you can try to specify the folder path in an environment variable and use that in your package. This example doesn't use configuration file (.dtsconfig) but it requires an Environment Variable named SSISFolderPath set up on the machine where the package is being executed.

Step-by-step process:

1. Navigate to Control Panel --> System --> Advanced system settings --> click on the Advanced tab --> Click on the Environment Variables... button --> Click on the second New... button at the bottom of the dialog. These steps are valid for Windows Server 2008 or 2008 R2. Older Windows Servers might have slightly different navigation options.

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

3. NOTE: If you have the Visual Studio IDE open, please close and re-open it so that the Environment variable settings are visible to the IDE as well as in the SSIS package.

4. On the SSIS package, create a variable named FilePath as shown in screenshot #2. Refer this link to know how to create a variable in SSIS package.

5. On the SSIS package, click on the menu SSIS and select Package Configurations... Please make sure that you click on the package before you do this. Otherwise, the option will not be visible.

6. Select Enable package condifurations checkbox and click Add... button. Refer screenshot #3.

7. On the Select Configuration Type step, select the value Environment variable from the drop down Configuration type and select the newly created variable, here in this example it is SSISFolderPath, from the dropdown Environment variable. Click Next > button. Refer screenshot #4.

8. On the Select Target Property step, expand the Variables section and expand the Properties of the variable FilePath and select Value under Properties node. Click Next > button. Refer screenshot #5.

9. On Completing the Wizard step, provide a suitable Configuration name. I have given Environment_Variable. Click Finish button. Refer screenshot #6.

10. I have placed a Script task on the Control Flow tab of the SSIS package just to demonstrate that the variable is populated with value from Environment Variables. Note that the variable currently has no value on the package. In the following step when the Script task is invoked during package execution, the variable will be populated with the value from Environment Variables SSIFolderPath created on the machine and will display it in a MessageBox. Refer screenshot #7 for sample output.

Hope that helps.

Script Task code: (Use the code given below to replace the Main() method in your 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# Main() method code that can be used only 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 this link to know how to create a variable in SSIS package.

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

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

回答2:

@Siva has a very good answer which works well for single environments/developers on different machines, however we have found in practise that if you run multiple environments (Dev/UAT/Prod/etc) on the same machine that using environment variables proves difficult, as you need to keep changing the environment variable before sub processes start.

We have tried a few config variations with various clients - only XML dtsConfig files having all the configuration inside (one for each environment) - an environment variable that controls which dtsConfig file is used - a minimal dtsConfig file that points to a SQL server/database/table combination that has full config defined.

If we started from scratch I would generally choose the third option now as it has been the most flexible for us.

回答3:

I've found that passing values into variables from SQL Server Agent works well for me.

I'm running SSIS packages on scheduled tasks, so the SQL Server Job Type is "SQL Server Integration Services Package", and then you can supply values on the "Set Values" tab of the Job Step Properties.

When I run locally for example, I have a property path of "\Package.Variables[User::ChildPackagePath].Properties[Value]" with a value of "D:\Database\ETL\ETL\" (No quotes on either). On the various environments this is different.

This has pros & cons -

The con is that anyone with access to SQL Server Agent jobs can see the details (Although only those people).

The pros is that you can script the job, and change any values in the script, to quickly create a new job on a new environment. Also you can have different 'versions' if you like - Where the same packages are used by different jobs using different values.