3 How to dynamically change…Connections
I would recommend familiarising yourself with ScriptPkg before reading this article:
This article will focus on changing Connection properties, for changing Task properties please see:
On with the article..
Each package contains a collection of Connections, which not surprisingly contains Connection objects.
Different connection objects use different Providers and have different ConnectionProperties.
To alter a connection we first have to identify it within the package, for which there are two main properties of interest:
The Name is what you call it when you first create the connection. The Name is unique to that
connection and therefore can be used to identify it within the package connections collection.
ProviderID specifies the underlying OLE DB provider, and you may have multiple connections
of the same type. The common ProviderID values are listed below:
|Microsoft.Jet.OLEDB.4.0||Jet e.g. Access & Excel|
If your data source is not listed above then create and save a sample package using that provider.
Then use ScriptPkg
and examine the output.
N.B. All of the examples below are written in ActiveX Script (VBScript),
and are designed for use within an Active Script Task. They will of course
function equally well in Visual basic with a few minor tweaks.
The following example examines a package, and changes all of the SQL Server connections
to use a server called “MyServer”, and a database called “MyDB”:
Option Explicit Function Main() ' Declare Variables Dim oPKG Dim oConnection ' Get Package Object Set oPKG = DTSGlobalVariables.Parent ' Examine Connections For Each oConnection in oPKG.Connections 'Test for SQL Server Connection If oConnection.ProviderID = "SQLOLEDB.1" Then 'Set new Server (DataSource) value oConnection.DataSource = "MyServer" 'Set new Database (Catalog) value oConnection.Catalog = "MyDB" End If Next ' Clear Up Set oConnection = Nothing Set oPKG = Nothing Main = DTSTaskExecResult_Success End Function
If you are only interested in a specific Connection you can reference it directly by name.
This simple example changes the file name property (DataSource) for a named Text File Connection.
Option Explicit Function Main() ' Declare Variables Dim oPKG Dim oConnection ' Get Package Object Set oPKG = DTSGlobalVariables.Parent ' Get Named Connection Set oConnection = oPKG.Connections("Text File (Source)") ' Set Filename to new value oConnection.DataSource = "X:YourPathYourFile.txt" Set oConnection = Nothing Set oPKG = Nothing Main = DTSTaskExecResult_Success End Function
Notice that in this case DataSource is not the server as it was
for the SQLOLEDB.1 provider, but the file name.
All connections have certain base properties as defined in SQL Server
Books Online, Connection Object (DTS).
To access the provider specific properties you need to delve into the ConnectionProperties,
also known as the OLEDBProperties collection.
This example changes the OLEDBProperty for a DTSFlatFile.1 provider column delimiter:
Option Explicit Function Main() ' Declare Variables Dim oPKG Dim oConnection Dim oOLEP ' Get Package Object Set oPKG = DTSGlobalVariables.Parent 'Get Named Connection Set oConnection = oPKG.Connections("Text File (Source)") ' Get the OLEDBProperty ("Column Delimiter") ' N.B. Column Delimiter only valid for Delimited Files ' "File Format" = 1 Set oOLEP = oConnection.ConnectionProperties("Column Delimiter") ' Set the Property Value oOLEP.Value = "|" ' Clear Up Set oOLEP = Nothing Set oConnection = Nothing Set oPKG = Nothing Main = DTSTaskExecResult_Success End Function
Unfortunately these OLEDB Properties are not defined in SQL Server Books Online. One can assume that this is
because they may change in future releases, but in the meantime they are very important in
designing a truly flexible package. To find out the specific properties you can use
ScriptPkg or a small utility I’ve written,
You will often want to set properties to values held elsewhere, my
Text File Connection file name article demonstrates methods
for GlobalVariables (DTS) and values held in a SQL table.