Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query works in SSMS but not in SSIS


Query works in SSMS but not in SSIS

Author
Message
Narly254
Narly254
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
JustMarie
JustMarie
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 1120
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
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8360 Visits: 19493
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
JustMarie
JustMarie
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 1120
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search