Solved

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

Posted on 2013-06-19
7
3,968 Views
Last Modified: 2016-02-11
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
Comment
Question by:alcsoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39261375
* 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
 

Author Comment

by:alcsoft
ID: 39262665
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 39264850
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:alcsoft
ID: 39264865
Gotcha,
so, why I am getting this error?
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 39265577
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
 

Author Comment

by:alcsoft
ID: 39279174
I had to change the datatype of VDate to string instead of datetime.
and that's took care of the issue.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39281060
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question