April 24, 2008 at 5:17 pm
I have a DTS package setup that uses an IBM DB2 ODBC connection and a transfer data link to a sql server. Both connections work just fine, but when I try to transfer the data, the system just can't find the data.
Here is the error message I'm seeing:
Error Description:[IBM][CLI Driver][DB2/AIX64SQL0519N. The prepare statement identifies the select or values statement of the open cursor "SQL_CURSH200C4. SQL State=24506
April 24, 2008 at 8:57 pm
Does it fail interactively as well as during scheduled running?
When you say the connections work fine, can you preview your data from IBM in the Transfer task?
April 30, 2008 at 4:58 pm
Yes, it does fail interactively as well as during scheduled running.
No, I cannot preview the data from IBM DB2 data in the Transfer task preview window.
Any other suggestions, I can look at?
May 1, 2008 at 6:28 am
That error message means that the "cursor" is already open but it is trying to be opened again.
I would suggest you try executing you select statement against the DB2 files directly and see if that works (this eliminates any potential issues with the DB2 end). If that works try a select statement against the DB2 files from SSIS but just do something simple like select * from XXX and see if that works. I work with DB2/SQL all the time and have notice that SSIS and DB2 can often be very picky about things that should not matter.
Also, why are you use the ODBC driver and not the OLEDB? Both MS and IBM have them but you will need Enterprise to use MS.
-Mike
May 13, 2008 at 6:11 pm
I'm currently have a dts package setup where I transfer data from a IBM DB2 server to our SQL Server. There are times the system recognizes both servers and I can transfer records, but when I can't I get the following error message:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217904 (80040E10)
Error string: [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error Detail Records:
Error: -2147217904 (80040E10); Provider Error: 99999 (1869F)
Error string: [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
I'm trying to run the dts command from the command line, but I get the error stated above.
Here is my dts run line:
SET @command = 'dtsrun /E /S"server_name" /N"dailysalessum" /A "@StartingDate":"8"="' + @Starting_Date + '" /A "@EndingDate":"8"="' + @Ending_Date + '" /W "0"'
EXEC master.dbo.xp_cmdshell @command
The @Starting_Date and @Ending_Date are variables I pass into this command and declare inside my stored procedure.
Does anybody have any input on how I can possibly fix this error?
May 13, 2008 at 6:31 pm
A few comments about the code to format the dtsrun.ex is:
SET @command = 'dtsrun /E /S"server_name" /N"dailysalessum" /A "@StartingDate":"8"="' + @Starting_Date + '" /A "@EndingDate":"8"="' + @Ending_Date + '" /W "0"'
Are @StartingDate and @EndingDate the name of the Global variables?
Is the datatype for the global variables defined as string or as date ? The type of "8" in the parameter means string.
If the global variable is a string, could there be a error on the conversion to a datetime datatype ? This could be occuring on either the SQL Server or the DB2 side.
SQL = Scarcely Qualifies as a Language
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply