Execute SQL Task not using parameter mapping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Execute SQL Task not using parameter mapping

Execute SQL Task not using parameter mapping

Rate Topic

Display Mode

Topic Options

Author

Message

sqlgirlatty

Posted Tuesday, October 16, 2012 3:04 PM

Valued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:34 PM
Points: 55, Visits: 169

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

Ray M

Posted Tuesday, October 16, 2012 3:31 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 1,469, Visits: 943

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

sqlgirlatty

Posted Wednesday, October 17, 2012 3:55 PM

Valued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:34 PM
Points: 55, Visits: 169

Ray,

Using an expression instead of direct input worked. Thanks for the tip! Smile

Post #1374074

« Prev Topic | Next Topic »

Permissions