Upsert using lookup to match output of OLEDB Command

ktjamms2
ktjamms2 used Ask the Experts™
on
I have a data flow task to do an update or insert depending on match or no match output from a lookup transformation in SSIS package.  

I have an OLE DB Source -->to a Data Conversion --> to a Pivot --> to a Lookup and split
match output to OLE DB Command
no match output to OLE DB Destination

In the OLE DB Command  I'm using an update query:
 UPDATE TableName SET
Field1 = ?,
Field2 = ?,
Field3= ?,
Field4  = ?,
Field5 = ?,
Field6 = ?,
Field7  = ?,
Field8 = ?,
Field9 = ?,
Field10 = ?,
Field11 = ?,
Field12 = ?,
Field13 = ?
WHERE ((Field14 = ?) AND (Field15= ?));

But it doesn't seem to be getting the parameters because an error stating that there is more than one data source column with the name "?" and that the data source column names must be unique.  I have my parameters set up in the lookup transformation editor and can see them when I click the "Parameters" button on the Advanced Custom query. I don't know what I'm doing wrong??

Any help would be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Can you post the custom query from the lookup?  Because that's where the error is coming from, right? (and not the OLE DB Command)

Author

Commented:
I'm not sure why it's not working, but the lookup appears to have everything setup correctly. I tried using both a custom and table selection on the Connection tab of the Lookup. The error is coming from the OLE DB Command when I'm trying to set up my sql command using the parameters/"?" ---> an error stating that there is more than one data source column with the name "?" and that the data source column names must be unique.


The Custom query on the advanced query is:

select * from
(select * from `tableName`) `refTable`
where
        `refTable`.`Field1` = ?
and `refTable`.` Field2` = ?
and `refTable`.` Field3` = ?
and `refTable`.` Field4` = ?
and `refTable`.` Field5` = ?
and `refTable`.` Field6` = ?
and `refTable`.` Field7` = ?
and `refTable`.` Field8` = ?
and `refTable`.` Field9` = ?
and `refTable`.` Field10` = ?
and `refTable`.` Field11` = ?
and `refTable`.` Field12` = ?
and `refTable`.` Field13` = ?
and `refTable`.` Field14` = ?
and `refTable`.` Field15` = ?
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Can you explain a bit more what the lookup is doing?  I mean, based on the above I don't see why you've activated the custom query.  If you want to check if a record exists for all 15 fields then you can just connect them on the Columns page.

But of course it would be better performance-wise if you could use a BK (business key) in that lookup instead of all fields in the table.

BTW: select * from tableName >> that's not a good idea, if the layout of tableName changes, the query will fail.  Better to list all field names.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
On the OLE DB, Column Mapping, Available Destination Columns is empty? I thought I needed to define them in the SqlCommand using parameters?

Author

Commented:
>>>BTW: select * from tableName >> that's not a good idea, if the layout of tableName changes, the query will fail.  Better to list all field names.<<<<<<<

Are you referring to the Custom query in the Lookup with that comment? That query  with the select * was built like that automatically by SSIS when I mapped the  Available Input Columns to the Available Lookup Columns on the Columns tab of the Lookup.  Are you suggesting that I change it?

Author

Commented:
OK so, I configured my Lookup to Full Cache which deactivates the Custom query on the Advanced tab.

I selected "Use a table or a view:" on the Connection tab and selected my destination table.

My unique identifier fields are field1 (an ID) and field2 (a date).  I joined only those two fields on the Columns tab.

I connected the No Match Output to an OLE DB Destination and connected the Lookup Match Output to an OLE DB Command.

Would you agree that should do it for the Lookup Configuration????

Then, I go to the OLE DB Command and configured my connection Manager for the destination database and put my UPDATE query in the SqlCommand of the Component Properties tab.

What should my update query look like????

Like this???

 UPDATE TableName SET
Field1 = ?,
Field2 = ?,
Field3= ?,
Field4  = ?,
Field5 = ?,
Field6 = ?,
Field7  = ?,
Field8 = ?,
Field9 = ?,
Field10 = ?,
Field11 = ?,
Field12 = ?,
Field13 = ?
WHERE ((Field14 = ?) AND (Field15= ?));
 

Shouldn't there be something in the Available Destination Columns box of the Column Mappings tab?????

Author

Commented:
My Lookup works great...I tested it with DataReaderDest/Data Viewers and it works exactly as it should with the amount of records expected in each. I guess what I am having the problem with is configuring the  OLE DB Command.

Any help will be awesome!
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
"Would you agree that should do it for the Lookup Configuration????"

If that ID and date uniquely define one record: yes.

"What should my update query look like????"

Looks like a valid update statement.

"Shouldn't there be something in the Available Destination Columns box of the Column Mappings tab?????"

Well, yes.  With that update statement you should be seeing 15 entries, the first called Param_0 and so on.  And the list in the bottom half of the screen should show 15 lines as well.  So you are not getting any of those?
Are you getting a warning in the yellow box right above the Refresh/OK/Cancel buttons?  (I know your initial question contains an error, is that still applicable?)
Ow, and your connection manager does point to a SQL Server database, right?
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
"I selected "Use a table or a view:" on the Connection tab and selected my destination table."

That's the reason why your custom query contained the "select *".  As a general best practice it's better to select the Use results of an SQL query radio button and use a SELECT statement that only returns the fields that the lookup needs, for instance SELECT YourID, YourDate from YourSchema.YourTable

This results in a faster component, less memory usage, less bandwidth if going over the network.
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
One more question: what is the provider in your connection manager's connection string?  (can be found through the Properties pane with the connection manager selected)

Author

Commented:
>>>Are you getting a warning in the yellow box right above the Refresh/OK/Cancel buttons?  (I know your initial question contains an error, is that still applicable?)
Ow, and your connection manager does point to a SQL Server database, right?<<<<<<

Yes still the same error:
"an error stating that there is more than one data source column with the name "?" and that the data source column names must be unique."

and

"There is nothing in the Available Destination Columns box of the Column Mappings tab?????" .....No Parameters are being listed

I'm connecting a linked dBase table in an Access database with another Access table in an Access database.

Provider: Native OLE DB\Microsoft Jet 4.0 OLE DB Provider

Author

Commented:
Provider of the source is:
Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider

Provider of the target:
Native OLE DB\Microsoft Jet 4.0 OLE DB Provider

Both connections pass connection tests

As I said earlier, everything seems to be working until I get to the OLE DB Command.

The OLE DB Source -->is going to a Data Conversion-->going to Pivot-->to Lookup-->Matched going to OLE DB Command and Unmatched going to OLE DB Destination.

I tested it with DataReaderDest/Data Viewers instead of OLE DB Command and it works exactly as it should with the amount of records expected
BI Consultant
Most Valuable Expert 2011
Commented:
Provider of the target:
Native OLE DB\Microsoft Jet 4.0 OLE DB Provider

Aha!  That's the problem: the syntax for the Jet driver is different than for SQL Server.  What happens if you change the provider to the one you're using in the source connection?  If that doesn't work, change your query to following syntax:

UPDATE TableName
SET Field1 = Input1
  , Field2 = Input2
  , ...  --all your other columns
WHERE Field14 = Input14 and Field15 = Input15

See also the comment in the following page: http://msdn.microsoft.com/en-us/library/ms141138(v=sql.90).aspx

Author

Commented:
how do I change it? it's grayed out

Author

Commented:
I made a new one, and changed the provider to match the source, but it didn't help. I've changed the query as you suggested, as so far it seems to have solved it! Thanks!
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
You're welcome!  Glad we got it sorted out :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial