?
Solved

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

Posted on 2013-06-19
7
Medium Priority
?
4,185 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

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

Accepted Solution

by:
DcpKing earned 2000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

801 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