I am new to SSIS. Trying to execute the Select Statement query stored in table's column value using SSIS OLEDB Source Component, as we have written the query directly in SSIS package.
If my Select Statement is 'Select col1, col2, col3 from Table1 where cond1=value1', I dont want to put it directly in OLE DB Source option 'SQL Command'.
Instead I want to store the Select Query as Column value in Table and then execute it by retrieving that Table's column value so that if there should be any change in column value or date range, I can directly do in table than in SSIS package.
Below is what I tired:
1. Created a SSIS variable and stored below query which works fine in SSMS:
DECLARE @Query nvarchar(4000)
SET @Query = (Select param_last FROM T_SYS_CNTRL where control_name='abd')
exec sp_executesql @Query
2. Select the option ‘SQL command from variable’ and the corresponding SSIS variable in OLE DB Source
3. Getting Error that No column information was returned by SQL. COuld you please help me here?
