Hi:

I am pumping data from an ODBC connection to Sybase to a regular OLEDB
connection to the local SQL Server using DTS. I am trying to use the
following code in the Transform Data Task:

SELECT * FROM dTable WHERE Timestamp > ?

I when I click on the "parameters" button in the Transform data task, I get
this error:
___________________________________________
Error Source: Microsoft OLE DB Provider for ODBC Drivers

Error Description: Provider cannot derive parameter information and
SetParameterInfo has not been called.
________________________________________

Any idea what the problem is here? Could it be that I am using a date
parameter against a datetime in Sybase? (For that matter, why can you only
set a global variable of type 'date' in SQL Server DTS when SQL Server only
allows datatypes of type 'datetime'?)

Thanks,
Kayda

It may be that the Sybase driver does not allow this type of operation

Try this instead


Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)


As to why DATE and not DATETIME.

Why anything? It would be impossible to specify all datatypes for all
systems (well not impossible but certainly cumbersome)



"Kayda" <blair863 (AT) hotmail (DOT) com> wrote

Thanks Allan:

I found your previous postings on this afterwards. One further question: Is
it possible to pass the name of the Transform data task as a parameter into
the function? For example, I want to use the same function to set the SQL on
separate transforms, so instead of recreating the function I want to pass it
the source tablename (which I know I can do) and the name of the Transform
Data task. I tried but it gave me a type mismatch error if I just insert the
parameter into the "Set oDataPump =
oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask " line.

Any idea?

Thanks,
Blair


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

So what you want to do is something like this

Function Main()


GenerateTaskSQL "DTSTask_DTSExecuteSQLTask_1", "Salmon"


Main = DTSTaskExecResult_Success
End Function



Function GenerateTaskSQL(TaskName, WhereClause)

DTSGlobalVariables.Parent.Tasks(Cstr(TaskName)).Cu stomTask.SQLStatement
= "SELECT 1 = 2 WHERE 'A' = '" & WhereClause & "'"

End Function


"Kayda" <blair863 (AT) hotmail (DOT) com> wrote