September 20, 2011 at 2:08 pm
Ok so I've got a rather large SSIS project I'm working with.
One of the data sources is the following SQL query. SSIS complains that no column information was returned by the SQL command.
I've added FMTONLY OFF to take care of temp tables, I've added NOCOUNT ON, Transaction Isolation is because this can be a lengthy read from a production system during the day, and I'd prefer not to prevent them from working on their stuff, I can always pick up an update later in the next run 1 hour later.
I've also set my AlwaysUseDefaultCodePage to true.
I even declared a table variable with all the columns I want to return and then selected out of it. How in the world can I still get the dreaded No column information was returned by the SQL command error?
This code works perfectly in SSMS, so it's an SSIS issue, I just don't know what tricks left to try.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET ANSI_WARNINGS OFF;
SET FMTONLY OFF;
SET NOCOUNT ON;
DECLARE @lastPMGnote DATETIME
SELECT
@lastPMGNote = MAX(CSSExtractDate)
FROM
cssdw.lastCSSExtraction
WHERE
CSSExtractType = 'PMGNote'
IF @lastPMGNote IS NULL
SET @lastPMGNote = 0
IF OBJECT_ID('tempdb..#pmg_notes') IS NOT NULL DROP TABLE #pmg_notes
CREATE TABLE #pmg_notes(
pmg INT NOT NULL,
pmgnote VARCHAR(MAX))
INSERT #pmg_notes(pmg,pmgnote)
SELECT
a.projectid,
ProjectNote = createuserid + ' ' + CONVERT(VARCHAR(MAX),DATEADD(hh,pubdata.dbo.get_utc_offset(a.CreateTimestamp),a.CreateTimestamp)) + CHAR(10) + CHAR(13) + CAST(a.Text AS VARCHAR(MAX))
from
tbl_pmg_Projectnote a
WHERE
a.CreateTimestamp >= @lastPMGnote
ORDER BY
a.projectid,
a.CreateTimestamp
DECLARE @pmgnote VARCHAR(MAX) =''
DECLARE @pmg INT =0
UPDATE #pmg_notes
SET
@pmgnote = pmgnote = CASE WHEN @pmg != pmg THEN pmgnote
ELSE @pmgnote + CHAR(10) + CHAR(13) + pmgnote END,
@pmg = pmg
IF OBJECT_ID('tempdb..#hold') IS NOT NULL DROP TABLE #hold
DECLARE @hold TABLE(
pmg INT NOT NULL,
createtimestamp DATETIME,
pmgnote VARCHAR(MAX))
;WITH cte_lastnotedate AS (
SELECT
projectid,
createtimestamp = MAX(createtimestamp)
from
tbl_pmg_projectnote
GROUP BY
projectid
)
INSERT @hold
( pmg, createtimestamp, pmgnote )
SELECT
pmg,
createtimestamp = CAST(MAX(b.createtimestamp) AS DATETIME),
pmgnote = CAST(MAX(pmgnote) AS VARCHAR(MAX))
FROM
#pmg_notes a LEFT JOIN cte_lastnotedate b ON
a.pmg = b.projectid
GROUP BY
pmg
SELECT
pmg,
createtimestamp,
pmgnote
FROM @hold
September 21, 2011 at 8:25 am
I finally solved this...
After all my attempts using an OLE DB Data Source,
I changed the data source for the queries that gave me this trouble to ado.net data sources, and all the problems went away.
March 14, 2014 at 11:21 am
I just wanted to say thanks for posting your solution for this. I had a fun couple of hours with trying to figure out what was happening with a new version of my query blowing up SSIS.
I had changed out my temp table to instead be a table variable. Along with that I was passing in set fmtonly off based on another post. No dice with any of that.
Everything started working when I switch over to an ADO.Net Source....I'm glad it's working but I don't understand why it was broken to begin with.
August 13, 2014 at 11:40 pm
Yep, solved my problem. Thanks!
July 15, 2015 at 4:23 am
Solved my issue too but i would like to understand why?
My query also worked direct on management studio but not through SSIS with OLEDB source \ destination
CODE:
--variables to hold each 'iteration'
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to 'loop' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0 and name not in ('master' , 'model')
--table variable to hold results
declare @vlfcounts table
(ServerInstance nvarchar (50),
dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert into @vlfcounts
values(@@SERVERNAME, @dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert into @vlfcounts
values(@@SERVERNAME, @dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
----output the full list
select serverinstance, dbname, vlfcount
from @vlfcounts
--drop table #vlfcounts
--order by dbname
February 25, 2016 at 11:31 am
I had the same issue, and it was because I was trying to use a #tmp table in the Dataflow task.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply