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

Execute SQL Task not using parameter mapping Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 3:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 2:14 PM
Points: 61, Visits: 194
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)
Post #1373540
Posted Tuesday, October 16, 2012 3:31 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020
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.
Post #1373550
Posted Wednesday, October 17, 2012 3:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 2:14 PM
Points: 61, Visits: 194
Ray,

Using an expression instead of direct input worked. Thanks for the tip!
Post #1374074
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse