[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4479
  • 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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