Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @.FileName and @.ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @.connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @.ConnectionPath is included.
The output of print is:
dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\BaseProfiles2.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\profiles.csv"
/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1\instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjy\MSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:\sysappl\CEM\SSIS\Imports\Trades\StaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1\instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@.FullPackagePath varchar(1000),
@.FileName varchar(500),
@.ConnectionPath varchar(1000)
as
declare @.returncode int
declare @.cmd varchar(1000)
declare @.FilePath varchar(1000)
declare @.FullFilePath varchar(1000)
set @.FilePath = 'd:\ApplData\CEM\WorkingTemp\'
set @.FullFilePath = @.FilePath + @.FileName
print ' -- ' + @.FileName
set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"'
set @.cmd = 'dtexec /f ' + @.FullPackagePath +
' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"
/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'
print @.cmd
set nocount on
begin try
exec @.returncode = master..xp_cmdshell @.cmd
end try
begin catch
exec @.LastGoodVersionSP
DECLARE @.msg nvarchar(200)
SET @.msg = ('Error during execute package')
EXECUTE uspErrorReporter @.msg
end catch
set nocount off
Did you get this resolved in one of your other threads, or is it still open?
