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;
