SQLTeam.com | Weblogs | Forums

SSIS Source Query Help


#1

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."


#2

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


#3

I am getting the same error.


#4

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.


#5

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
  )
);
;