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  General SQL Server Forums  New to SQL Server Programming  Having problems dynamically setting the connection

Author  Topic  Wendy W Starting Member 1 Post Posted - 2009-08-05 : 16:57:50 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