?
Solved

SSIS Update OLE DB Command Parameter Syntax Error

Posted on 2010-08-26
5
Medium Priority
?
4,334 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 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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