pass variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

pass variables

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-08-08 : 03:57:01

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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-08 : 06:04:46

The identifier error is because you are running the dtexec command rather than using xp_cmdshell to execute the string.

Get the package to log the variable and the connection setting then try the value manually.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-08-08 : 06:19:52

quote:
Originally posted by nr

The identifier error is because you are running the dtexec command rather than using xp_cmdshell to execute the string.

Get the package to log the variable and the connection setting then try the value manually.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

If it is done manually then the error is as above i.e.
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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-08 : 06:23:51

>> the @connectionPath variable is hardcoded inside the package then package runs fine.

So what's the difference between the variable that works and the one that fails.
Suspect you have the wrong format as it looks like it is looking for an odbc datasource.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources