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  Other SQL Server Topics (2005)  set connectin string by Xml

Author  Topic  nyy0723 Starting Member 4 Posts Posted - 2009-09-06 : 22:41:20 hi all, I use the script task to set connection by the encode xml. it can work at SSIS design environment. And it also can work at dos command line: dtexec /f "c:\PACKAGE\TEST.dtsx " /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V >>C:\LOG\test.log" But when I use the stored procedure to execute the command as bellow sp_cmdshell @dtexec_cmd it failed. I trace the script task. I found the the value is null. It seems the value is null at xmlfile. connProperty.SetValue(conn, ConnectionInfo.getConnectionString(xmlFile, conn.Name, "connectionstring")) It is so strange. Can you help me? Thanks for your help in advance. The script task is as bellow, ================================================================= Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Public Class ScriptMain Public Sub Main() Dim env As Environment Dim xmlFile As String = env.GetEnvironmentVariable("SSISConf_BSR") Dim conns As Microsoft.SqlServer.Dts.Runtime.Connections = Dts.Connections Dim conn As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = conns(0) Dim connProperty As Microsoft.SqlServer.Dts.Runtime.DtsProperty Try For Each conn In conns If (conn.CreationName = "ODBC" Or conn.CreationName = "OLEDB" Or conn.CreationName = "FTP") Then For Each connProperty In conn.Properties If connProperty.Name = "ConnectionString" Then connProperty.SetValue(conn, ConnectionInfo.getConnectionString(xmlFile, conn.Name, "connectionstring")) ElseIf connProperty.Name = "UserName" Then connProperty.SetValue(conn, ConnectionInfo.getConnectionString(xmlFile, conn.Name, "username")) ElseIf connProperty.Name = "Password" Then connProperty.SetValue(conn, ConnectionInfo.getConnectionString(xmlFile, conn.Name, "password")) End If Next End If Next conn Catch ex As Exception MsgBox(ex.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub End Class ====================================================================