Query works in SSMS but not in SSIS

  • 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.

  • 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.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

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

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