Solved

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

Posted on 2013-06-19
7
3,696 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

803 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