SSIS, DTEXEC,Problem with dynamically setting the connectionstring

Question

SSIS, DTEXEC,Problem with dynamically setting the connectionstring

wendy_wang

SSC Veteran

Points: 240
More actions
August 5, 2009 at 2:55 pm

#385125

Hi,
I having problems dynamically setting the connectionstring using the \SET command option:
EXEC xp_cmdshell 'dtexec.exe /f "E:\SSISPackage\DBPackage.dtsx"
"/set "\Package.Connections[Data Destination].Properties[ConnectionString];
\""Data Source=SQLSeverName;User ID=UserName;Password=UserPass;Initial Catalog=DBName;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;"\"
"
'
The error I am getting as below,can anyone help?
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 3:53:32 PM
Error: 2009-08-05 15:53:34.23
Code: 0xC0202009
Source: RSF_DBSyncPackage 2 Connection manager "DBSync Data Destination"
Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Invalid authorization specification".
End Error
Error: 2009-08-05 15:53:34.25
Code: 0xC00291EC
Source: Execute SQL Task Execute SQL Task
Description: Failed to acquire connection "DBSync Data Destination". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2009-08-05 15:53:34.25
Code: 0x80019002
Source: RSF_DBSyncPackage 2
Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCoun
t or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:53:32 PM
Finished: 3:53:34 PM
Elapsed: 1.296 seconds
NULL
Thanks,
Wendy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

gtschech Ten Centuries Points: 1351 More actions · Answer 1

Wendy,

this error most often occurs when the account specified exists in Windows, but is not associated with any SQL server rights. It could be that you have forgotten to specify the domain of the user, misspelled the user name or did not add it to the group that has rights on the SQL server, or any likewise error.

WM_HOPETHATHELPS

Guenter

wendy_wang SSC Veteran Points: 240 More actions · Answer 2

Hi Guenter ,

Thank you for your help.

I am using SA account, not windows’ domain account. If I have all user info plugged in the package, it can run successfully. When pass in the parameters by connectionstring, it doesn’t not work. Does anything to do with package protection level?

Thanks,

Wendy