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:
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.
Create an environment variable as shown in screenshot #1. I have named it as SSISFolderPath and gave the value c:\temp\SSISFolderPath\
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.
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.
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.
Select Enable package condifurations checkbox and click Add... button. Refer screenshot #3.
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.
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.
On Completing the Wizard step, provide a suitable Configuration name. I have given Environment_Variable. Click Finish button. Refer screenshot #6.
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)
