Solved

SSIS Update OLE DB Command Parameter Syntax Error

Posted on 2010-08-26
5
4,269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

628 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