February 17, 2005 at 10:42 am
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
February 17, 2005 at 10:45 am
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 ?
February 17, 2005 at 12:16 pm
I need a second connection to read a different table than that being read as the input to the transformation.
February 17, 2005 at 1:13 pm
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.
February 17, 2005 at 1:23 pm
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.
February 18, 2005 at 1:26 am
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.
February 18, 2005 at 10:45 am
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