Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4773
  • Last Modified:

CLI0100E Wrong number of parameters. SQLSTATE=07001 (IBM OLE DB Provider for DB2)

I am getting this error in SSIS 2008 when I am trying to execute an SSIS pkg which
- Transfer data from a table in DB2 to a table in SQL 2008R2
- It pulls only the data which are newer than a specific date which is passed as parameter to OLE Source.

[Stable [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "IBM OLE DB Provider for DB2"  Hresult: 0x80004005  Description: " CLI0100E  Wrong number of parameters. SQLSTATE=07001".
[SSIS.Pipeline] Error: component "Stable" (1) failed the pre-execute phase and returned error code 0xC0202009.

Here is what I did.
Create 2 tasks, one is (Execute SQL Task) and the other is Data Flow Task

p1.jpg
The Read date task will read the date from a table and inset it into a variable.

ReadDate-Task.jpg
Here is how I set the output to variable

ReadDate-SetOutput.jpg

Now, I want to use this variable as parameter in OLE data source
So I created a data flow

Dataflow.jpg
Then use OLE source editor to set the query and the parameter.

OLESOURCE.jpg
When I execute the pkg it failed in OLE Source and give the above error, I attached the package as well.
If I don’t use parameter it run successfully, What I miss?
0
alcsoft
Asked:
alcsoft
  • 4
  • 3
1 Solution
 
DcpKingCommented:
* In the source (from DB2) make the source a variable (in source, Data Access Mode is SQL Command from Variable).

* Get rid of the middle task for now (you don't say what it does).

* Make yourself a string variable with the full query (including a date) in it and test it.

* Now make that same variable a composition of the code you already have, but, in place of the static date, the date variable you have that has the right date. (if it's always today or yesterday, etc., then you can make that variable automatically populate when the job runs).

hth

Mike
0
 
alcsoftAuthor Commented:
Mike,
Thanks for the response, I got a question tho
Why I need to use a variable as a sourse in Data Access mode, can't we simply use the date variable as a parameter to SQL command in the OLE DB Source?
0
 
DcpKingCommented:
You can, but I just found it easier to work it this way when I tried it out before posting it. I find it easier to do things like testing with a static version and then adding the dynamic parts using a variable. It limits the bits I'm touching at one time - I invariably screw things up if I try to change more than two things at once, and sometimes even one thing is too many.      {grin}


hth

Mike
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
alcsoftAuthor Commented:
Gotcha,
so, why I am getting this error?
0
 
DcpKingCommented:
It claims that you're missing a variable. What type is your VDate? I don't have a DB2 system runnable right now to try things out, but try either a datetime or a string. Datetime is what you'd expect, but, as you're using it with a command that's in text it may be wanting a piece of text.

hth

Mike
0
 
alcsoftAuthor Commented:
I had to change the datatype of VDate to string instead of datetime.
and that's took care of the issue.
0
 
DcpKingCommented:
Sorry that there isn't a rule for these things, but (especially between different systems) you often just have to experiment. Very often getting things down to absolute basics is the only way, despite all the nifty tricks the designers try to add in for us.

Happy it helped

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now