Sql-server – Passing a list to a SQL query in SSIS

oledbsql serverssis

I need to be able to pass a list of undefined size that will contain numbers like so ('1900'),('1920'),… to and OLE DB source in SSIS.

as of right now I am trying a lot of different things that aren't working.

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?

select
      fl.[Version]
      ,fl.ObjectID
      ,[FieldLogID]
      ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
      ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...

...
where fleja.FieldLog_FieldLogID IN (@fieldLogID)

The @fieldLogID will contain all the numbers that are passed in. The numbers format does not matter so much, they can be strings, ints, etc. Just as long as we are passing a list.

Any tips on how this is done?

Best Answer

Have you tried using dynamic sql? Something like this:

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?
declare @sql nvarchar(1000) = ''

SET @sql = 'select fl.[Version]
           ,fl.ObjectID
           ,[FieldLogID]
           ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
           ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...
           ...
           where fleja.FieldLog_FieldLogID IN (' + @fieldLogID + ')'
EXEC (@sql)

The syntax may be a little off and the @sql nvarchar variable type would probably need to be expanded more. The format for @fieldLogID would need to be something like this though:

@fieldLogID = n'''1800'',''1900'',''2000'''

Instead of executing the @sql variable, you can always print it to the screen to verify your syntax. Using this approach I would be a little worried about the @fieldLogID variable and I would like to validate that it is correct and nothing malicious is going in there.

Related Question