Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4539
  • 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 RadConsultant, TrainerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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