Query works in SSMS but not in SSIS

Question

Query works in SSMS but not in SSIS

Narly254

SSC Veteran

Points: 256
More actions
February 25, 2014 at 9:25 am

#288648

I have a tried two different variants to update a table. I am trying to update a date column that in the database is a char (8) field, displayed as yyyymmdd and in the Excel Source it is displayed as d/m/yyyy. In SSIS 2010 the query and package work fine. However, in SSIS 2005 it does not work. I've tried using OLE DB command, OLE DB Destination using SQL command, and OLE DB Destination using variable as string with evaluate as expression set to true. When using the first command declaring variables I get the error
Declare
@SchBGN Char (8)
Set @SchBGN = ?
UPDATE Table1
SET Table1.Start = CASE WHEN Table2.ForwardSchedule = 1 THEN @SchBGN Else ' ' END,
Table1.Complete = Case WHEN Table2.BackwardSchedule = 1 THEN @SchBGN ELSe ' ' END
From Table1, Table2
WHERE Table1.RELEASE_WO = Left (Table2.JobNumber + ' ', 10) + Table2.OrderNumber
AND Table2.jobnumber = ?
And Table2.OrderNumber = Right (Table1.RELEASE_WO, 4)
"failed with the following error: "Syntax error, permission violation, or other nonspecific error"."
The other command that I have used all three approaches mentioned above is this one. The error I get here is Multipart identifier.... Which I do not see anywhere. Especially doesn't make sense to me because it works inside of SSMS.
UPDATE Table1
SET Table1.Start = CASE WHEN Table2.ForwardSchedule = 1 THEN ? Else ' ' END,
Table1.Complete = Case WHEN Table2.BackwardSchedule = 1 THEN ? ELSe ' ' END
From Table1, Table2
WHERE Table1.RELEASE_WO = Left (Table2.JobNumber + ' ', 10) + Table2.OrderNumber
AND Table2.jobnumber = ?
And Table2.OrderNumber = Right (Table1.RELEASE_WO, 4)
I know this is a lot and I hope I made it clear what I'm trying to do. Thank you in advance for any replies and all of the help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JustMarie SSCertifiable Points: 7771 More actions · Answer 1

I've all but given up on the OLE DB Command transformation. I've switched to stage tables and stored procedures. The data flows in the majority of my packages are simply to get the data to the stage table where I can use other processes to do my magic.

This assumes that you have a place to put the data on the destination server so you can run all the T-SQL you need to do the work.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

JustMarie (2/26/2014)
I've all but given up on the OLE DB Command transformation...

Good choice, for me you can go further and remove the words 'all but'. It's usually too slow as it works in row-by-row fashion.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

JustMarie SSCertifiable Points: 7771 More actions · Answer 3

I try to avoid absolutes in things (except cursors) so there may be somewhere, some time, a need for the OLE DB Command transformation. It's an available option and rather than turn people away from it by saying "don't use it" I'll say I don't use it any more.