SSIS Source Query Help

Hi Guys,

I want to use below Query on my OLE DB Source in SSIS

SET FMTONLY OFF
DECLARE @cmd AS NVARCHAR(4000)

SET @cmd = (Select SqlQuery from SQLSource
Where id = 1)
--EXEC (@cmd);
EXEC sp_executesql @cmd;

I am reading Source SQL Query from the table and Data Element should always be same. Please advise.

Here is the error that I am getting, BTW I am using 2014.

'Unable to retrieve column information from the data source. Make sure your target table in the database is available."

SSIS uses SET FMTONLY on to execute your query to get the column info. However, you are explicitly disabling that. That's probably why.

I am getting the same error.

Here is my Sample Data

--Step 1
Create Table QueryTest
(
ID INT,
Fname Varchar(20),
State Varchar(20)
)

Insert into QueryTest
Select '1','Smith','CA'
Union
Select '2','James','NY'

Select * from QueryTest

--Step 2
Create Table QueryLogTable
(
ID INT,
SQLQuery Varchar(2000)
)

Insert into QueryLogTable
Select '1','Select * from QueryTest'

Select * from QueryLogTable

What I want, I want to use SQL Query in Query Log Table as a OLE DB Source in SSIS. Please guide me. Thank You.

Since you want to use dynamic sql (why?), you'll need to use the with results set syntax like this:

declare @sql nvarchar(4000);
set @sql = N'select 1 as val';
exec sp_executesql @sql
WITH RESULT SETS
(
  (
    val INT
  )
);
;