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 ?
February 26th, 2011 9:14am