Dynamic SQL in SSIS sub query parameter

I have a parameter in inside a sub query of a SQL command data source. I am trying to put this in a variable of string type since SQL command cannot take parameters in a sub query. Below is my SQL and I cannot parse due to some error. Can someone please help. Thanks in advance.

"select  [SpecificationAttributeID]
      ,[SpecificationID]
      ,[StatusID]
      ,[AttributeID]
      ,[Version]
      ,[AddedDescription]

      
from Lewcmesdb1.webspecdc.dbo.SpecificationAttributes sa with (nolock)
where exists (select '1'
     from Lewcmesdb1.webspecdc.dbo.Samples s with (nolock)
     where s.SpecificationAttributeID = sa.SpecificationAttributeID
    and s.SavedSampleTime >= dateadd(dd,'" +@[User::Day]+"'"",getdate()))"
August 19th, 2015 2:24pm

What datatype is @User::Day? It needs to be converted to a string if not a string.

You also seem to have got carried away with quotes - ]+"'"",getdate()))"

How many do you want? You don't need to put quotes around "@[User::Day]" as an integer is expected by SQL

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 4:08pm

It is an integer. Now I tried this but still it doesn't work. Please help. Thanks in advance.

"select  [SpecificationAttributeID]
      ,[SpecificationID]
      ,[StatusID]
      ,[AttributeID]
      ,[Version]
      ,[AddedDescription]

      
from Lewcmesdb1.webspecdc.dbo.SpecificationAttributes sa with (nolock)
where exists (select '1'
     from Lewcmesdb1.webspecdc.dbo.Samples s with (nolock) 
     where s.SpecificationAttributeID = sa.SpecificationAttributeID)
	 and s.SavedSampleTime >= dateadd(dd," + @[User::Day]+ ",getdate()))"
August 19th, 2015 4:19pm

Try this

"dateadd(dd," +(DT_WSTR,2)@[User::Day]+ ",getdate()))"

length depends up how big the number is.

in short use type casting function of @[User::Day] variable to string.

  • Marked as answer by czarvk 9 hours 11 minutes ago
Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 5:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics