Combine config-paramters with parameters passed from commanline

Question

I have created a SSIS-package that imports a file into a table (simple enough). I have some variables, a few set in a config-file such as server, database, importfolder. at runtime I want to pass the filename. This is done through a stored procedure using dtexec. When setting the paramters throught the configfile it works fine also when setting all parameters in the procedure and passing them with the \Set statement (se below). when I try to combine the config-version with settings parameters on the fly I get an error refering to the config-files path that was set at design time.

Has anybody come across this and found a solution for it?

Regards Frederik

DECLARE @SSISSTR            VARCHAR(8000),
        @DataBaseServer     VARCHAR(100),
        @DataBaseName       VARCHAR(100),
        @PackageFilePath    VARCHAR(200),
        @ImportFolder       VARCHAR(200),
        @HandledFolder      VARCHAR(200),
        @ConfigFilePath     VARCHAR(200),
        @SSISreturncode     INT;

/* DEBUGGING
    DECLARE  @FileName VARCHAR(100),
             @SelectionId INT
    SET @FileName = 'Test.csv';
    SET @SelectionId = 366; 
*/  
SET @PackageFilePath = '/FILE "Y:\SSIS\Packages\PostalCodeSelectionImport\ImportPackage.dtsx" ';
SET @DataBaseServer = 'STOSWVUTVDB01\DEV_BSE';
SET @DataBaseName = 'BSE_ODR';
SET @ImportFolder = '\\Stoswvutvbse01\Application\FileLoadArea\ODR\\';
SET @HandledFolder = '\\Stoswvutvbse01\Application\FileLoadArea\ODR\Handled\\';
--SET @ConfigFilePath =  '/CONFIGFILE "Y:\SSIS\Packages\PostalCodeSelectionImport\Configuration\DEV_BSE.dtsConfig" ';

----now making "dtexec" SQL from dynamic values
SET @SSISSTR = 'DTEXEC ' + @PackageFilePath;    -- + @ConfigFilePath;
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::SelectionId].Properties[Value];' + CAST( @SelectionId AS VARCHAR(12));    
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::DataBaseServer].Properties[Value];"' + @DataBaseServer + '"';
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::ImportFolder].Properties[Value];"' + @ImportFolder + '" ';
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::DataBaseName].Properties[Value];"' + @DataBaseName + '" ';
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::ImportFileName].Properties[Value];"' + @FileName + '" ';  
SET @SSISSTR = @SSISSTR + ' /SET \Package.Variables[User::HandledFolder].Properties[Value];"' + @HandledFolder + '" ';   

-- Now execute dynamic SQL by using EXEC.       
    EXEC @SSISreturncode = xp_cmdshell @SSISSTR;    

Answer 1

Assuming that all variables except User::ImportFileName are set in your configfile, the following should work:

DECLARE @SSISSTR            VARCHAR(8000),
        @PackageFilePath    VARCHAR(200),  
        @ConfigFilePath     VARCHAR(200), 
        @SSISreturncode     INT; 
        @FileName           VARCHAR(100)

SET @FileName = 'Test.csv'; 
SET @PackageFilePath = '/FILE "Y:\SSIS\Packages\PostalCodeSelectionImport\ImportPackage.dtsx" '; 
SET @ConfigFilePath =  '/CONFIGFILE "Y:\SSIS\Packages\PostalCodeSelectionImport\Configuration\DEV_BSE.dtsConfig" '; 

--now making "dtexec" SQL from dynamic values 
SET @SSISSTR = 'DTEXEC ' + @PackageFilePath + @ConfigFilePath; 
SET @SSISSTR = @SSISSTR + '/SET "\Package.Variables[User::ImportFileName].Properties[Value]";"' + @FileName + '" ';   

-- Now execute dynamic SQL by using EXEC.        
    EXEC @SSISreturncode = xp_cmdshell @SSISSTR;   

The differences from your code are:

1. My code doesn't set the values for variables set in the config file
2. The string "\Package.Variables[User::ImportFileName].Properties[Value]" is quoted in my version