Excel SSIS查询在Excel中返回空列，但在Management Studio中不返回 [英] Excel SSIS query returns null columns in Excel but not in Management studio
问题描述
我竭尽所能浏览网络，但是这个问题使我难以理解.我在SSIS中有一个可以正常运行的存储过程.它做了很多事情，最终返回了一些数字和文本.该过程本身使用#temp表，因为在proc运行之后数据不需要存在，并返回〜931K行.
下一步是将proc的输出转换为excel.使用MS查询，我调用包含必要参数的proc.它可以运行，但我获得的唯一数据是带有数字的列.我缺少文本值.我认为这可能是从SSIS到Excel的文本翻译问题，因此我将输出从nvarchar更改为varchar，问题仍然存在.我编写了proc，以便可以进行必要的更改.另外，我认为这可能是临时表的问题，因此我尝试构建表，使用proc在其中插入数据，然后将该表拉入Excel，虽然我得到了更多的文本列，但仍有一些空白.
有什么建议吗?
问题的短版: SQL在Management Studio中有效，但文本不会返回给excel.完成导入/更新后，proc中的行数与Excel中的行数匹配.这些数字按预期返回.
版本:
Excel:2007年 -SQL Server:2005年 -管理工作室:2008R2 -使用MS查询的ODBC连接-
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
此问题的解决方案归结为数据类型.如果您像我一样知道目标Excel，则必须使用Excel可以转换的数据类型.我一直在使用nvarchar(max)，它没有被带到Excel中，当我将字段更改为text和char时，我感觉很好.一旦知道要查找的内容，便从Microsoft找到了这个答案:数据类型限制.另一个问题是我使用的是存储过程而不是纯SQL，尽管直接从表中进行选择也存在问题.我尝试加载表，而不是依赖具有类似故障的存储过程.没有任何错误返回，这只是没有数据.通过我的测试，以下是文本/字符类型的转换及其成功:
文本-作品
ntext-作品
char-作品
nchar-作品
varchar-失败
nvarchar-失败
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
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
这篇关于Excel SSIS查询在Excel中返回空列，但在Management Studio中不返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
