Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4395
  • Last Modified:

SSIS Update OLE DB Command Parameter Syntax Error

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
Lweighall
Asked:
Lweighall
1 Solution
 
LweighallAuthor Commented:
In addition, this is one of my references:

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

0
 
lundnakCommented:
0
 
vdr1620Commented:
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
 
Reza RadCommented:
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
 
LweighallAuthor Commented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now