Not sure if this is the correct way to call SSIS package with filename and staging table as parameters... it fails rite now
declare @cmd varchar(1000)
declare @ssispath varchar(1000)
declare @filePath varchar(1000)
declare @stagingtable varchar(1000)
set @ssispath = 'C:\Visual Studio 2005\Projects\Test\Test\Data Import SB.dtsx'
set @filePath = 'C:\test.txt'
set @stagingtable = 'u_mart.dbo.test'
select @cmd = 'dtexec /F "' + @ssispath + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::InputFile].Properties[Value];"' + @filePath + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::stagingTableName].Properties[Value];"' + @stagingtable + '"'
print @cmd
------------------ This is the error
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 5:21:39 AM
Warning: 2009-07-24 05:22:09.76
Code: 0x80012017
Source: Data Import SB
Description: The package path referenced an object that cannot be found: "\Package.Variables[User::stagingTableName].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \Package.Variables[User::stagingTableName].Properties[Value] value to u_mart.dbo.test.
Started: 5:21:39 AM
Finished: 5:22:09 AM
Elapsed: 30.39 seconds
NULL
dtexec /F "C:\Visual Studio 2005\Projects\Test\Test\Data Import SB.dtsx" /SET \Package.Variables[User::InputFile].Properties[Value];"C:\test.txt" /SET \Package.Variables[User::stagingTableName].Properties[Value];"u_mart.dbo.test"