Script ODBC with ADO
-
Has anyone used ADO to access host data via ODBC in an Activx script transformation? I cannot get the connection to open. I am building a connection string
DSN = FM20GL; Database =M20CPMDBSN;UID = xxxx;PWD=xxxx
and setting the connectionobject.connectionstring property to this string
the open fails
Where FM20GL is my ODBC connection name. THis works in VB but fails in the transformation.
Does anyone have any idea
-
Why do you need to open the connection inside script, versus, say, having a connection object in the DTS package with the ODBC properties set at design-time ?
-
I need a second connection to read a different table than that being read as the input to the transformation.
-
DTS supports this functionality via Lookups in the transformation task.
They are configured on a separate tab of the transformation UI:
They can run on a different connection object than the transformation itself.
-
I am familiar with lookups. However, I need to customize the SQL for parameters that the ODBC driver will not accept as drivers. I cannot get this to work. I tried constructing the sql in the tranformation script and replacing it, but I don't get good results.
I am trying yet another solution.
-
Try adding "PROVIDER=MSDASQL" to the connection string.
By the way. To get your connection settings right, add a connection to the dts packages and open the disconnected edit screen. Scroll to the connection node in the treeview and you will read all connection parameters.
HABIB.
-
That fixed it,
Thanks very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply