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
@lastPMGNote = MAX(CSSExtractDate)
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,
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))
a.CreateTimestamp >= @lastPMGnote
DECLARE @pmgnote VARCHAR(MAX) =''
DECLARE @pmg INT =0
@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,
;WITH cte_lastnotedate AS (
createtimestamp = MAX(createtimestamp)
( pmg, createtimestamp, pmgnote )
createtimestamp = CAST(MAX(b.createtimestamp) AS DATETIME),
pmgnote = CAST(MAX(pmgnote) AS VARCHAR(MAX))
#pmg_notes a LEFT JOIN cte_lastnotedate b ON
a.pmg = b.projectid