Export (0) Print
Expand All

ConnectionManager.ConnectionString Property

Gets or sets the connection string used to establish a connection to a data source.

Namespace:  Microsoft.SqlServer.Dts.Runtime
Assembly:  Microsoft.SqlServer.ManagedDTS (in Microsoft.SqlServer.ManagedDTS.dll)

'Declaration
<RefreshPropertiesAttribute(RefreshProperties.All)> _
Public Property ConnectionString As String 
	Get 
	Set
'Usage
Dim instance As ConnectionManager 
Dim value As String 

value = instance.ConnectionString

instance.ConnectionString = value

Property Value

Type: String
A String that contains the connection string value.

The format and contents of the connection string depend on the data provider, the data source, and the type of connection manager that is being created. To view a sample connection string, in SQL Server Data Tools, create a package, and then create a connection manager of the appropriate type within that package. Next, in the Properties window, examine the ConnectionString property of the connection manager that you just created.

The following code example adds an OLE DB connection manager, and then sets the connection manager name and connection string properties.

' Add the OLE DB connection manager.
Dim adventureWorks As ConnectionManager =  package.Connections.Add("OLEDB") 
' Set stock properties.
adventureWorks.Name = "OLEDBConnection"
adventureWorks.ConnectionString = "Provider=SQLNCLI10;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=(local);Auto Translate=False;"

For an XML connection string, the connection string value is formatted similar to the following:

String myConnString = @"C:\Program Files\Microsoft SQL Server\orders.xml";

Whereas for an OLE DB connection, the connection string contains values similar to the following connection string:

String myConnString = "Server=MYSERVER;Provider=SQLOLEDB.1;Pwd= xxxxxxx;User ID= xxxxx;Initial Catalog=mySource;OLE DB Services=-2";

And an ODBC connection string may look similar to the following:

String myConnString = "DSN=LocalServer;DATABASE=MySource;PWD=xxxxxxx;UID=xxxxx";

In the properties dialog box of a connection in the SSIS Designer, this value appears as the Connection String property.

For a list of all connection managers included with the product, see Integration Services (SSIS) Connections.

The following code example creates a FileSystemTask as an Executable in a package. The FileSystemTask copies a test folder that contains two subfolders and a .txt file, to another folder. The source and destinations are existing folders. Therefore, the IsDestinationPathVariable and IsSourcePathVariable are set to false, and the Destination and Source properties are set to reference two FILE connection managers that have been created and have had their connection strings set to reference Strings containing the paths.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask
 
Namespace FileSystemTask_API
    Class Program
        Shared  Sub Main(ByVal args() As String)
            Dim sourceDir As String =  "C:\TestFolder" 
            Dim destDir As String =  "C:\MyNewTestFolder" 
 
            Dim pkg As Package =  New Package() 
            Dim exec1 As Executable =  pkg.Executables.Add("STOCK:FileSystemTask") 
            Dim th As TaskHost =  exec1 as TaskHost 
 
            ' Show how to set properties using the TaskHost Properties.
            ' Set the properties to copy an existing folder, which contains two subfolders
            ' and a .txt file, to another existing folder on the C:\ drive.
 
            ' The source or destination files are not in a variable, so set 
            ' IsSourcePathVariable and IsDestinationPathVariable to false.
            th.Properties("IsSourcePathVariable").SetValue(th, False)
            th.Properties("IsDestinationPathVariable").SetValue(th, False)
 
            ' Create the File connection manager for the source.
            Dim cm As ConnectionManager =  pkg.Connections.Add("FILE") 
            cm.Name = "The FILE connection manager"
            cm.ConnectionString = sourceDir
            cm.Properties("FileUsageType").SetValue(cm, DTSFileConnectionUsageType.FolderExists)
 
            ' Create the File connection manager for the destination.
            Dim cmdest As ConnectionManager =  pkg.Connections.Add("FILE") 
            cmdest.Name = "The destination FILE connection manager"
            cmdest.ConnectionString = destDir
            cmdest.Properties("FileUsageType").SetValue(cmdest, DTSFileConnectionUsageType.FolderExists)
 
            ' Set the source property and destination properties
            ' for the task.
            th.Properties("Source").SetValue(th, cm.Name)
            th.Properties("Destination").SetValue(th, cmdest.Name)
 
            ' The operation to perform is to copy all the files and
             ' subfolders in a folder.
            ' Do not overwrite the destination information 
            ' if it exists.
            th.Properties("Operation").SetValue(th, DTSFileSystemOperation.CopyDirectory)
            th.Properties("OperationName").SetValue(th, "Copy TestFolder")
            th.Properties("OverwriteDestinationFile").SetValue(th, False)
 
            ' Set the attribute of the folder to be read-only.
            th.Properties("Attributes").SetValue(th, DTSFileSystemAttributes.ReadOnly)
            ' Multiple attributes can be set. The next line of code,
            ' commented out, shows how to do that.
            'th.Properties["Attributes"].SetValue(th, DTSFileSystemAttributes.ReadOnly | DTSFileSystemAttributes.Hidden);
 
            ' Run the task and copy the folder.
            Dim result As DTSExecResult =  pkg.Execute() 
        End Sub
    End Class
End Namespace

Community Additions

ADD
Show:
© 2014 Microsoft