October 16, 2012 at 3:04 pm
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)
October 16, 2012 at 3:31 pm
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.
October 17, 2012 at 3:55 pm
Ray,
Using an expression instead of direct input worked. Thanks for the tip!:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply