Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query works in SSMS but not in SSIS Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 12:06 PM
Points: 6, Visits: 33
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.

Post #1544999
Posted Wednesday, February 26, 2014 10:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 273, Visits: 613
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.
Post #1545488
Posted Thursday, February 27, 2014 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 5,078, Visits: 11,861
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1545737
Posted Friday, February 28, 2014 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 273, Visits: 613
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.
Post #1546467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse