Excel SSIS запрос возвращает пустые столбцы в Excel, но не в Management Studio

Question

Я сделал все возможное, чтобы посмотреть в Интернете, но эта проблема ускользает от меня. У меня есть хранимая процедура в SSIS, которая работает нормально. Он делает кучу вещей, в конечном итоге возвращает некоторые цифры и текст. Сама процедура использует таблицы #temp, так как данные не должны существовать за пределами цикла выполнения и возвращает ~931K строк.

Следующим шагом было вывести результаты процесса в Excel. Используя запрос MS, я вызываю процедуру, включающую необходимые параметры. он работает, но единственные данные, которые я получаю, - это столбцы с числами. Я пропускаю текстовые значения. Я подумал, что это может быть проблема перевода текста из служб SSIS в Excel, поэтому я изменил вывод с nvarchar на varchar, и проблема остается. Я написал процедуру, чтобы я мог внести любые необходимые изменения. Кроме того, я подумал, что это может быть проблема с временной таблицей, поэтому я попытался создать таблицу, вставить в нее данные с помощью процедуры, затем перетащить эту таблицу в Excel, и хотя я получил еще несколько текстовых столбцов, число все еще оставалось пустым.

Есть какие-нибудь предложения?

Краткая версия проблемы: SQL работает в студии управления, но текст не возвращается в Excel. Число или строки из процедуры соответствуют количеству строк в Excel после завершения импорта / обновления. Цифры возвращаются, как и ожидалось.

Версии:

Excel: 2007 - SQL Server: 2005 - Студия управления: 2008R2 - Соединение ODBC с использованием MS-запроса -

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

Решение этой проблемы сводилось к типам данных. Если вы знаете, что ваш конечный Excel, как я, то вам придется использовать тип данных, который Excel может преобразовать. Я использовал nvarchar (max), который не был перенесен в Excel. Когда я изменил поля на text и char, я был в порядке. Я нашел этот ответ от Microsoft, когда знал, что искать: типы данных Microsoft Excel. Была также страница об ограничениях: Ограничения Типа данных. Другая часть заключалась в том, что я использовал хранимую процедуру, а не чистый SQL, хотя была также проблема с выбором непосредственно из таблицы. Я пытался загрузить таблицу, а не полагаться на хранимую процедуру с похожими сбоями. В этом ничего не было возвращено, просто данных не было. Благодаря моему тестированию, здесь приведены преобразования типа текст / символ и их успех:

текст - работы
ntext - работает
чар - работает
нчар - работает
varchar - не удалось
nvarchar - не удалось

Answer 2

Я предполагаю, что процедура делает окончательный выбор из #results, и желаемые результаты видны в анализаторе запросов SQL Server Management Studio; но затем, когда вы вызываете процедуру в потоке данных служб SSIS, текстовые данные исчезают на пути к месту назначения Excel?

Вот несколько вещей, чтобы попробовать. Используйте средство просмотра данных (оно находится на стрелке между шагами), чтобы убедиться, что ваши текстовые столбцы содержат нужный текст ниже по потоку от вызова процедуры. Используйте производный виджет столбца, чтобы поместить текст в нужный тип данных (например, кодовую страницу 1252 для VARCHAR). Служба SSIS привередлива к типам данных.

Наконец, попробуйте другой пункт назначения, например, простой текстовый файл, такой как CSV-файл. Вы можете использовать производный виджет столбца для конкатенации и добавления запятых и двойных кавычек вокруг текста и всего, что вы хотели бы попробовать. Затем вы можете увидеть в Блокноте, получаете ли вы желаемый результат, и Excel должен иметь возможность открыть файл CSV.

Раньше для записей Excel было ограничение в 64 000, но я полагаю, что в Excel 2007 этого уже давно нет. Я продолжаю использовать ограничение в 2000 КБ, вырезая и вставляя вывод в Блокнот (он молча терпит неудачу, что разочаровывает, пока я не помню). Но ваши записи 931K могут достигать некоторого другого предела, поэтому попробуйте меньшие выходные наборы и посмотрите, работают ли они по-другому.