Setting parameter information?

Question

Setting parameter information?

Narly254

SSC Veteran

Points: 256
More actions
January 22, 2014 at 12:20 pm

#284772

So, I'm attempting to upload certain columns in an excel document to a table in my database. I've done it before however this time I'm using a query to reference a different table for the routing id column.
UPDATE PPOPERF SET Set_up_Time = ?, Run_Time = ? FROM PPOPERF
WHERE EXISTS (SELECT * FROM WOPRoutingOperations wopr, WorkOrders wo
WHERE wo.DivisionId = 'Jobscope'
AND wopr.DivisionId = 'Jobscope' AND wopr.RoutingId = ?
AND wopr.routingSequenceNumber = ?
AND wo.JobNumber = wopr.JobNumber
AND wo.OrderNumber = wopr.OrderNumber
AND wo.Complete = 0 AND PPOPERF.Release = wopr.JobNumber
and PPOPERF.work_order = wopr.ordernumber and PPOPERF.operation = wopr.partneroperation)
and PPOPERF.divisionid = 'Jobscope'
This is my query. I know it works because I have tested in in Visual studio 2010. Here is my problem: In Visual studio 2005 I have the exact same set up, but it gives me this error "Parameter information cannot be derived from SQL statement with sub select queries. Set parameter information before preparing command."
I'm using a simple set up. Excel Source---Data Conversion (for the routing ID column) and an OLE DB Command
If anyone knows a work around or knows exactly how to fix this issue that would be EXTREMELY helpful!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

I'm using a simple set up. Excel Source---Data Conversion (for the routing ID column) and an OLE DB Command
If anyone knows a work around or knows exactly how to fix this issue that would be EXTREMELY helpful!!

The OLEDB Command component is a horrible performer - you would do well to re-engineer the solution.

You could resolve your problem by loading the entire data set to a staging table and then running a stored proc - with whatever params you need - to perform the UPDATE as a set-based operation.

You'll see package run times improve.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Narly254 SSC Veteran Points: 256 More actions · Answer 2

Thank you It took some time but that helped a lot!

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Narly254 (1/23/2014)
Thank you It took some time but that helped a lot!

Well done. Quality coding takes time, but pays off in the long run 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Narly254 SSC Veteran Points: 256 More actions · Answer 4

So I figured out a way to use the OLE DB Command just to see the time difference and WOW... You are very correct, that package was impeccably slow!!