How to import varchar(max) into SSIS string variable.
Database table
SSIS Varaibles
SSIS query result set object
The foreach loop has to have something in it otherwise the variables don’t get populated.
Execute SQL Task set up. There is only one record returned from the query. Make sure the the SQLStatement handles NULLs via ISNULL(xxx,'') or COALESCE(xxx,'')
Foreach loop setup.
Declare @maxRowLength int = 50;
ReplyDeletewith x as (
select 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s. Over the years, sometimes by accident, sometimes on purpose (injected humour and the like).' data
),
tmp(data,c,rn) as (
select data,SUBSTRING(data, 1,@maxRowLength),@maxRowLength
from x
union all
select data,SUBSTRING(data,rn,@maxRowLength),rn + @maxRowLength
from tmp
where rn < len(data)
)
select c,rn from tmp
OPTION (MAXRECURSION 500);
you can split a large text to smaller chunks, in the rows and load them in foreach loop container,
ReplyDelete