DTS Package - Connection to MySQL DB
Hi,
May be the driver to, because I personally encountred problems with my sql drivers, so try an older driver
The complexity resides in the simplicity
February 26th, 2011 7:54am
Hi,
Sorry I wasn't sure if DTS packages are still covered here. If this is the wrong forum then please advise where I can go.
I'm setting up a DTS package to connect to a number of MySQL databases.
Setting up MySQL connection :
(a). Select an Other Connection
(b). Select Data Source - MySQL ODBC 5.1 Driver
(c). Create a System DSN with all the MySQL connection details.
(d). Save the DTS task.
When I perform (d). the Data Source field changes from "MySQL ODBC 5.1 Driver" to "Other ODBC Data"
Additionally when I used Disconnected Edit to view the connection properties there are around 14 OLE DB Properties
After I perform a run there are probably 100 OLE DB Properties.I'm assuming that most of these new entries are related to the execution of the task. One of these properties now is "Data Source Name".The field "Data Source Name" has the
DSN name .... and does not change with each run, currently we are only manipulating Data Source.
See an example of the code below how we set the connection properties (this is successful for other implementations though with SQL Server Connections) :
DTSGlobalVariables("gv_RecordCount").Value = 0
ConnGrp=Split(DTSGlobalVariables("gv_STORE_DB_STRING").Value,"|")
Countries=Split(DTSGlobalVariables("gv_STORE_COUNTRY_STRING").Value)
if Cint(DTSGlobalVariables("gv_ARRAY_POSITION").Value) <= CInt(Ubound(Countries)) then
DTSGlobalVariables("gv_CURRENT_CTRY").Value = Countries(DTSGlobalVariables("gv_ARRAY_POSITION").Value)
set oConnection = pkg.Connections("Store-Database Connection")
ConnDB=Split(ConnGrp(DTSGlobalVariables("gv_ARRAY_POSITION").Value),",")
For counter = 0 to UBound(ConnDB)
If counter = 0 Then
oConnection.DataSource = ConnDB(counter)
' oConnection.DataSourceName = ConnDB(counter)
' Msgbox (ConnDB(counter) )
End If
If counter = 1 Then
oConnection.Catalog = ConnDB(counter)
' Msgbox (ConnDB(counter) )
End If
If counter = 2 Then
oConnection.UserID = ConnDB(counter)
' Msgbox (ConnDB(counter) )
End If
If counter = 3 Then
oConnection.Password = ConnDB(counter)
' Msgbox (ConnDB(counter) )
End If
next
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if
Is it possible to manipulate the Data Source Name property of the OLE DB Properties of this connection ? Or perhaps is something not set up correctly ?
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 9:14am