September 5, 2005 at 3:49 pm
All of techniques for parameterizing DTS packages work OK when moving data between SQL Server databases or an SQL Server database and text files, etc. However, our institution uses Sybase databases for much of the information I want to move into SQL Server databases.
Moving the data work OK until I try to parameterize a query with Sybase as the source and SQL Server as the target. For instance, when I click on the "Parameter" button in the "Transform Data Task Properties" dialogue box in a DTS package, the error: Microsoft OLE DB Provider for ODBC Drivers: Provider cannot derive parameter information and SetParameterInfo has not been called". I also can't use a Dynamic Properties task or a WHERE clause that references a value retrieved by an OPENROWSET statement; all of these techniques give some kind of error when used in the portion of the DTS package that references the Sybase connection.
Any ideas on how to fix this?
September 6, 2005 at 7:08 am
I've run into similar issues using an IBM DB2 OLEDB provider in DTS packages, albeit with a different error message. The workaround I developed entailed setting global variables to the appropriate parameter values using a SQL Task or ActiveX Script task as appropriate then using an ActiveX Script task to set the query for the transform step (these steps exposed as Data Pump operations). When you look at the transform step after executing the ActiveX script task the query will appear as if you had hardcoded values into the where clause but in reality these values were dynamically set.
At the moment I can't get into example code but will try to post an update later. Hope this helps.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply