Solved

SSIS Update OLE DB Command Parameter Syntax Error

Posted on 2010-08-26
5
4,149 Views
Last Modified: 2013-11-10
I have developed an SSIS package that performs an "UPSERT". There is a data source (Sharepoint List), a Lookup on a certain ID (which I've already got working, and then if the lookup match fails it inserts rows just fine; however, when the lookup finds a match it goes to an OLE DB Command object which I'm having trouble configuring.

The issue I have is I keep getting Syntax errors returned from the SQL server because I am using question marks as input parameters in my SQL statement:

UPDATE dbo.Deployments
SET
      Status = ?,
      FirstName = ?,
      LastName = ?,
      Email = ?,
      ExternalUserID = ?,
      DeploymentLocation = ?,
      DeploymentDate = ?,
      EndDate = ?,
      Hardware = ?,
      Accessories = ?,
      Monitors = ?,
      OS = ?,
      Software = ?,
      SpecialRequests = ?,
      PM = ?,
      AssignedTo = ?,
      Ticket = ?,
      SubmittedDate = ?,
      WHERE SharepointListID = ?

According to all the tutorials I have read, this is supposed to work. I have defined External Columns and have been able to successfully map all the columns properly; however, when it goes to execute it always bombs out with this error:

[UPDATE Deployments Table [593]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Syntax error, permission violation, or other nonspecific error".

It's definately not a permissions issue. I've already worked that out. What am I doing wrong?

0
Comment
Question by:Lweighall
5 Comments
 

Author Comment

by:Lweighall
ID: 33537850
In addition, this is one of my references:

http://msdn.microsoft.com/en-us/library/ms141773.aspx

0
 
LVL 7

Expert Comment

by:lundnak
ID: 33538257
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33538354
I see that there is a comma " , " after the last column before WHERE .. remove it..Also, make sure that columns and parameters have same data types..of course should be mapped correctly

Try the code below
UPDATE dbo.Deployments 
SET
      Status = ?,
      FirstName = ?,
      LastName = ?,
      Email = ?,
      ExternalUserID = ?,
      DeploymentLocation = ?,
      DeploymentDate = ?,
      EndDate = ?,
      Hardware = ?,
      Accessories = ?,
      Monitors = ?,
      OS = ?,
      Software = ?,
      SpecialRequests = ?,
      PM = ?,
      AssignedTo = ?,
      Ticket = ?,
      SubmittedDate = ?
      WHERE SharepointListID = ?

Open in new window

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33539463
in addition to what vdr1620 said about removing last comma before Where word,
It it always better to create a stored procedure of your update statement and then call that stored procedure in ole db command,
pros of this way is that when you go to column mapping tab of ole db command, you will see parameter names there instead of parameter1,parameter2 ,.... . so your package will have high readability .( this is best practice )
0
 

Author Closing Comment

by:Lweighall
ID: 33543841
The comma was it. I knew I had to be missing something simple... it just didn't make sense. Thanks for pointing it out.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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