Excel SSIS query returns null columns in Excel but

Question

I did my best to look around the web but this problem eludes me. I have a stored procedure in SSIS that works fine. It does a bunch of stuff eventually returning some numbers and text. The procedure itself uses #temp tables since the data does not need to exist beyond the proc run and returns ~931K rows.

The next step was to bring the output of the proc into excel. Using MS query, I call the proc including the necessary parameters. it runs but the only data I get back is the columns with numbers. I am missing the text values. I thought it might be a text translation issue from SSIS to Excel so I changed the output from nvarchar to varchar and the problem remains. I wrote the proc so I can make any changes necessary. Also, I thought that it might be a temp table issue so I tried building a table, inserting the data there using the proc then pull that table into Excel and while I got a few more text columns, a number were still blank.

Are there any suggestions?

Short version of the problem: SQL works in management studio but text is not returned to excel. The number or rows from the proc match the number of rows in Excel when it is finished importing/updating. The numbers come back as expected.

Versions:

Excel: 2007 - SQL Server: 2005 - Management studio: 2008R2 - ODBC connection using MS query -

USE [cmdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [estimate].[sp_calendar]( 
    @calendar_start char(8),
    @years as int   
    )
as

set nocount on;

declare @calendar_end char(8)
declare @actual_start_date datetime
declare @actual_end_date datetime
declare @loop_counter datetime

set @actual_start_date = CONVERT (datetime, @calendar_start, 112)
set @loop_counter = @actual_start_date
set @actual_end_date = dateadd(year,+@years,@actual_start_date)
set @calendar_end = cast(year(@actual_end_date) as char(4))+RIGHT('00'+ CONVERT(VARCHAR,month(@actual_end_date)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(@actual_end_date)),2)

begin
create table #calendar (
    [yearmonth] nvarchar(8)
)
end

begin
    create table #results (
        [actual ExpectedActionDt] datetime
        ,[calc ExpectedActionDt] ntext
        ,ExpectedActionDt datetime
        ,[calc IntegratedReleasePlanDt] ntext
        ,IntegratedReleasePlanDt datetime
        ,[key] ntext
        ,projectid ntext
        ,projectnm ntext
        ,ParentChaseProjectNo ntext
        ,VersionTag ntext
        ,itemid ntext
        ,Qty float
        ,ItemNotes ntext
        ,CashflowType ntext
        ,frequency  ntext
        ,UnitPrice float
        ,[cost] float
        )
end

begin
    create table #baseline (
    [actual ExpectedActionDt] datetime
    ,[calc ExpectedActionDt] nvarchar(8)
    ,ExpectedActionDt datetime
    ,[calc IntegratedReleasePlanDt] nvarchar(8)
    ,IntegratedReleasePlanDt datetime
    ,[key] ntext
    ,projectid ntext
    ,projectnm ntext
    ,ParentChaseProjectNo ntext
    ,VersionTag ntext
    ,itemid ntext
    ,Qty float
    ,ItemNotes ntext
    ,CashflowType ntext
    ,frequency ntext
    ,UnitPrice float
    ,[cost] float)
end 

insert into #calendar (
        [yearmonth])
        select 
        distinct calendarid [yearmonth]
    from 
        [cmdb_core].[dbo].[Calendar] 
    where 
        calendarid between @calendar_start and @calendar_end

    insert into #baseline (
        [actual ExpectedActionDt]
        ,[calc ExpectedActionDt]
        ,ExpectedActionDt
        ,[calc IntegratedReleasePlanDt]
        ,IntegratedReleasePlanDt
        ,[key]
        ,projectid
        ,projectnm
        ,ParentChaseProjectNo   
        ,VersionTag
        ,itemid
        ,Qty
        ,ItemNotes
        ,CashflowType
        ,frequency
        ,UnitPrice
        ,[cost])        
    select  
        case
            when (ExpectedActionDt is not null)
                then ExpectedActionDt
            when (IntegratedReleasePlanDt is not null)
                then IntegratedReleasePlanDt
            else
                DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
        end [actual ExpectedActionDt]
        ,case
            when (ExpectedActionDt is not null)
                then cast(year(ExpectedActionDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(ExpectedActionDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(ExpectedActionDt)),2)
            when (IntegratedReleasePlanDt is not null)
                then cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2)
            else
                cast(year(getdate()) as char(4))+'0101'
        end [calc ExpectedActionDt]
        ,ExpectedActionDt
        ,cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2) [calc IntegratedReleasePlanDt]
        ,IntegratedReleasePlanDt
        ,cast(ModelEstimateId as nvarchar(max))+cast(BucketId as nvarchar(max))+cast(ItemNo as nvarchar(max)) [key]
        ,projectid
        ,projectnm
        ,ParentChaseProjectNo   
        ,VersionTag
        ,itemid
        ,Qty
        ,ItemNotes
        ,CashflowType
        ,frequency
        ,UnitPrice
        ,case
            when frequency = 'OneTime'
                then Qty
            else
                cast(round((UnitPrice*Qty)/12,0) as int)
            end [cost]
    from 
        estimate.ComputedEstimates
    where
        [status] <> 'Hold'
        and CostCategory <> 'Assembly'
        and includeinforecast = 'Y'
        and case
            when (ExpectedActionDt is not null)
                then cast(year(ExpectedActionDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(ExpectedActionDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(ExpectedActionDt)),2)
            when (IntegratedReleasePlanDt is not null)
                then cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2)
            else
                cast(year(getdate()) as char(4))+'0101'
            end >= @calendar_start


WHILE (@loop_counter <= @actual_end_date)
BEGIN
insert into #results (
        [actual ExpectedActionDt]
        ,[calc ExpectedActionDt]
        ,ExpectedActionDt
        ,[calc IntegratedReleasePlanDt]
        ,IntegratedReleasePlanDt
        ,[key]
        ,projectid
        ,projectnm
        ,ParentChaseProjectNo
        ,VersionTag
        ,itemid
        ,Qty
        ,ItemNotes
        ,CashflowType
        ,frequency
        ,UnitPrice
        ,[cost])
select * from #baseline where [actual ExpectedActionDt] >= @loop_counter

set @loop_counter = dateadd(day,+1,@loop_counter)
END


select 
    c.[yearmonth]
    ,a.[calc ExpectedActionDt]
    ,a.[key]
    ,a.projectid
    ,a.projectnm
    ,a.ParentChaseProjectNo 
    ,a.VersionTag
    ,a.itemid
    ,a.ItemNotes
    ,a.CashflowType
    ,a.frequency
    ,a.Qty
    ,a.UnitPrice
    ,a.[cost]
from
    #calendar as c  
    left outer join
    #results a
    on c.[yearmonth] = a.[calc ExpectedActionDt]
order by 1,2,3

drop table #baseline
drop table #results
drop table #calendar

Answer 1

I'm guessing the procedure does a final select from #results, and the desired results are visible in SQL Server management studio query analyzer; but then, when you call the procedure in an SSIS dataflow, the text data disappears on its way into your Excel Destination?

Here are a few things to try. Use the data viewer (that's on the arrow between steps) to check that your text columns contain the desired text, downstream from the procedure call. Use the derived column widget to put the text into the desired datatype (e.g., code page 1252 for VARCHAR). SSIS is finicky about datatypes.

Finally, try a different Destination, for example a flat text file such as a csv file. You can use the derived column widget to do concatenation and add commas and double-quote marks around the text and whatever else you would like to try. Then you can see in Notepad whether you're getting the desired output, and Excel should be able to open a csv file.

There used to be a 64,000 limit on Excel records, but I believe that's long gone in Excel 2007. I keep hitting the 2000KB limit cutting and pasting output into Notepad (it fails silently, which is frustrating, until I remember). But your 931K records may be hitting some other limit, so try smaller output sets and see whether those work any differently.

Answer 2

The solution to this issue came down to data types. If you know that your destination Excel, as I did, then you have to use a data type that Excel can convert. I had been using nvarchar(max) which wasn't being brought over to Excel, When I changed the fields to text and char, I was good. I found this answer from Microsoft once I knew what to look for: Microsoft Excel Data Types. There was also a page on limitations: Data Type Limitations. The other piece was that I was using a stored procedure rather than pure SQL although there was also a problem with selecting directly from the table. I tried to load a table rather than relying on the stored procedure with similiar failures. No errors were returned in any of this, it was just no data. Through my testing, here are the text/character type conversions and their success:

text - works
ntext - works
char - works
nchar - works
varchar - failed
nvarchar - failed