SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute SQL Task not using parameter mapping


Execute SQL Task not using parameter mapping

Author
Message
sqlgirlatty
sqlgirlatty
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 230
I'm doing a fairly straight foward insert command with a parameter in the select clause, but the query doesn't seem to read the value in my variable and I cannot tell what I've done incorrectly.
The connection type is OLE DB,
SQLSourceType = Direct input

Variable Name
Parameter User::TrackForms Input 0 100

INSERT INTO mmm.request_track (RECEIPT_NBR, LOCKBOX_OUT_NBR, LOCKBOX_RECEIPT_DT ,FORM_ID,PTQ_ID,ZIP_CD,FCO_TXT)
SELECT e.RECEIPT_NUMBER ,e.BEN_OUT_NUMBER
,CONVERT(date,e.RECEIVED_DATE,120)AS LOCKBOX_RECEIPT_DT
,FORM_ID = (SELECT TOP 1 FORM_ID FROM mmm.process_form WHERE FORM_CD = FORM_NUMBER)
,PTQ_ID = (SELECT ON_SUCCESS_PTQ_ID FROM mmm.process_workflow WHERE FORM_ID = (SELECT FORM_ID FROM mmm.process_form WHERE FORM_CD = FORM_NUMBER))
,ZIP_CD = LEFT(e.BEN_ZIP,5)
,FCO_TXT = (SELECT DISTINCT FCO_OFFICE_CD FROM mmm.zipcode_ref WHERE ZIP_CD = LEFT(e.BEN_ZIP,5) AND FCO_OFFICE_CD IS NOT NULL AND RETIRED_ZIPCODE_IND = 'False')
FROM mmm.ecis_tmp e
WHERE e.FORM_NUMBER IN (?)
AND NOT EXISTS(SELECT RECEIPT_NBR FROM mmm.request_track WHERE RECEIPT_NBR = e.RECEIPT_NUMBER)
Ray M
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 1076
I don't think the IN is going to work the way your trying to use it.
Whats happening behind the scenes is SSIS Is converting your sql to Dynamic sql and executing it with sp_ExecuteSql and passing in the string as 1 value.

Try putting your sql into a variable by modifying it as an expression, and put in the values in the in clause that way.
sqlgirlatty
sqlgirlatty
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 230
Ray,

Using an expression instead of direct input worked. Thanks for the tip!:-)
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