Exporting Stored Procedure Results w/ SSIS?

External Since: Jun 11, 2008 Posts: 9

Hello,

I'm working with a stored procedure written by our software vendor and
trying to get the results of this stored procedure exported into some kind of
file so i can load it up on another server for reporting purposes.

The stored proc uses the FOR XML RAW command and I had lots of trouble
finding information on exporting XML data from SQL 2005 using SSIS. As far
as I can tell at this point SSIS can't do hardly anything I want it to
without having to sit down for 3 weeks beating my head against the wall
trying to figure out every little detail.

That I need to save for when I Have time to do nothing productive for 3
weeks...

I wanted to just execute the stored proc and jam the results into a file,
SSIS does not like that since it was outputting FOR XML RAW.

SO i re-created the SP without that parameter.

If i execute the following query, I get a result set just fine:

create table #advisortemp
(
group_id int,
group_desc nvarchar(50),
group_type nvarchar(1),
display_column nvarchar(1),
display_row int,
info_text nvarchar(100),
info_href nvarchar(100),
group_image nvarchar(max),
image_height int,
image_width int,
metric_id int,
metric_desc nvarchar(100),
sum_metric_value nvarchar(100),
metric_format nvarchar(1),
am_display_row int,
am_display_column int,
link_href nvarchar(100),
metric_type nvarchar(1),
info_href_unread_image nvarchar(25),
info_href_unread_image_height int,
info_href_unread_image_width int,
info_href_read_image int,
info_href_read_image_height int,
info_href_read_image_width int,
metric_scope nvarchar(1),
metric_type_image int,
metric_type_image_height int,
metric_type_image_width int,
metric_type_image_tooltip nvarchar(60),
range_type nvarchar(1),
metric_desc_tooltip nvarchar(60)
)
insert into #advisortemp
(
group_id,
group_desc,
group_type,
display_column,
display_row,
info_text,
info_href,
group_image,
image_height,
image_width,
metric_id,
metric_desc,
sum_metric_value,
metric_format,
am_display_row,
am_display_column,
link_href,metric_type,
info_href_unread_image,
info_href_unread_image_height,
info_href_unread_image_width,
info_href_read_image,
info_href_read_image_height,
info_href_read_image_width,
metric_scope,metric_type_image,
metric_type_image_height,
metric_type_image_width,
metric_type_image_tooltip,
range_type,
metric_desc_tooltip
)
exec dbo.sv_ng_advisor '0060', null, '2643', null

select * from #advisortemp
GO

I get this error if i plug it in as a query to specify data to transfer in
SSIS on my SQL 2005 Enterprise x64 SP2 system. I cannot go any further.

TITLE: SQL Server Import and Export Wizard
------------------------------

The statement could not be parsed.

------------------------------
ADDITIONAL INFORMATION:

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name '#advisortemp'. (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------

If i run this query and replace temp table w/ table variable, it allows me
to parse the statement and it also returns the result set. But running it in
SSIS, it always exports 0 rows even though everything says it runs fine...

declare @advisor TABLE
(
group_id int,
group_desc nvarchar(50),
group_type nvarchar(1),
display_column nvarchar(1),
display_row int,
info_text nvarchar(100),
info_href nvarchar(100),
group_image nvarchar(max),
image_height int,
image_width int,
metric_id int,
metric_desc nvarchar(100),
sum_metric_value nvarchar(100),
metric_format nvarchar(1),
am_display_row int,
am_display_column int,
link_href nvarchar(100),
metric_type nvarchar(1),
info_href_unread_image nvarchar(25),
info_href_unread_image_height int,
info_href_unread_image_width int,
info_href_read_image int,
info_href_read_image_height int,
info_href_read_image_width int,
metric_scope nvarchar(1),
metric_type_image int,
metric_type_image_height int,
metric_type_image_width int,
metric_type_image_tooltip nvarchar(60),
range_type nvarchar(1),
metric_desc_tooltip nvarchar(60)
)
insert into @advisor
(
group_id,
group_desc,
group_type,
display_column,
display_row,
info_text,
info_href,
group_image,
image_height,
image_width,
metric_id,
metric_desc,
sum_metric_value,
metric_format,
am_display_row,
am_display_column,
link_href,metric_type,
info_href_unread_image,
info_href_unread_image_height,
info_href_unread_image_width,
info_href_read_image,
info_href_read_image_height,
info_href_read_image_width,
metric_scope,metric_type_image,
metric_type_image_height,
metric_type_image_width,
metric_type_image_tooltip,
range_type,
metric_desc_tooltip
)
exec dbo.sv_ng_advisor '0060', null, '2643', null
select * from @advisor
GO

What am i doing wrong? Is there an easier way to export the rows returned
by the stored procedure? Why doesn't my table variable return any rows even
though it runs fine in query analyzer? Why isn't the SSIS import/export
wizard smart enough to realize i put a temp table in the query?

Is there some trick to exporting data from a stored proc that uses the FOR
XML RAW format into a flat file or whatever other file is doable?

I'm totally disappointed w/ SSIS. I don't understand why everyone says its
so much better than DTS was. I have had nothing but trouble trying even to
use the wizards from it. I never had any issues using DTS in the past on
2000.

In adddition, for having all this wonderful XML integration, SQL 2005 sure
doesn't seem to provide much for exporting out of the box. How integrated is
that?

External Since: Jun 11, 2008 Posts: 9

I guess i had the programming group highlighted instead of integration
services.

Should I re-post this there?

"redi311" wrote:

> Hello,
>
> I'm working with a stored procedure written by our software vendor and
> trying to get the results of this stored procedure exported into some kind of
> file so i can load it up on another server for reporting purposes.
>
> The stored proc uses the FOR XML RAW command and I had lots of trouble
> finding information on exporting XML data from SQL 2005 using SSIS. As far
> as I can tell at this point SSIS can't do hardly anything I want it to
> without having to sit down for 3 weeks beating my head against the wall
> trying to figure out every little detail.
>
> That I need to save for when I Have time to do nothing productive for 3
> weeks...
>
> I wanted to just execute the stored proc and jam the results into a file,
> SSIS does not like that since it was outputting FOR XML RAW.
>
> SO i re-created the SP without that parameter.
>
> If i execute the following query, I get a result set just fine:
>
> create table #advisortemp
> (
> group_id int,
> group_desc nvarchar(50),
> group_type nvarchar(1),
> display_column nvarchar(1),
> display_row int,
> info_text nvarchar(100),
> info_href nvarchar(100),
> group_image nvarchar(max),
> image_height int,
> image_width int,
> metric_id int,
> metric_desc nvarchar(100),
> sum_metric_value nvarchar(100),
> metric_format nvarchar(1),
> am_display_row int,
> am_display_column int,
> link_href nvarchar(100),
> metric_type nvarchar(1),
> info_href_unread_image nvarchar(25),
> info_href_unread_image_height int,
> info_href_unread_image_width int,
> info_href_read_image int,
> info_href_read_image_height int,
> info_href_read_image_width int,
> metric_scope nvarchar(1),
> metric_type_image int,
> metric_type_image_height int,
> metric_type_image_width int,
> metric_type_image_tooltip nvarchar(60),
> range_type nvarchar(1),
> metric_desc_tooltip nvarchar(60)
> )
> insert into #advisortemp
> (
> group_id,
> group_desc,
> group_type,
> display_column,
> display_row,
> info_text,
> info_href,
> group_image,
> image_height,
> image_width,
> metric_id,
> metric_desc,
> sum_metric_value,
> metric_format,
> am_display_row,
> am_display_column,
> link_href,metric_type,
> info_href_unread_image,
> info_href_unread_image_height,
> info_href_unread_image_width,
> info_href_read_image,
> info_href_read_image_height,
> info_href_read_image_width,
> metric_scope,metric_type_image,
> metric_type_image_height,
> metric_type_image_width,
> metric_type_image_tooltip,
> range_type,
> metric_desc_tooltip
> )
> exec dbo.sv_ng_advisor '0060', null, '2643', null
>
> select * from #advisortemp
> GO
>
> I get this error if i plug it in as a query to specify data to transfer in
> SSIS on my SQL 2005 Enterprise x64 SP2 system. I cannot go any further.
>
> TITLE: SQL Server Import and Export Wizard
> ------------------------------
>
> The statement could not be parsed.
>
> ------------------------------
> ADDITIONAL INFORMATION:
>
> Deferred prepare could not be completed.
> Statement(s) could not be prepared.
> Invalid object name '#advisortemp'. (Microsoft SQL Native Client)
>
> ------------------------------
> BUTTONS:
>
> OK
> ------------------------------
>
>
> If i run this query and replace temp table w/ table variable, it allows me
> to parse the statement and it also returns the result set. But running it in
> SSIS, it always exports 0 rows even though everything says it runs fine...
>
> declare @advisor TABLE
> (
> group_id int,
> group_desc nvarchar(50),
> group_type nvarchar(1),
> display_column nvarchar(1),
> display_row int,
> info_text nvarchar(100),
> info_href nvarchar(100),
> group_image nvarchar(max),
> image_height int,
> image_width int,
> metric_id int,
> metric_desc nvarchar(100),
> sum_metric_value nvarchar(100),
> metric_format nvarchar(1),
> am_display_row int,
> am_display_column int,
> link_href nvarchar(100),
> metric_type nvarchar(1),
> info_href_unread_image nvarchar(25),
> info_href_unread_image_height int,
> info_href_unread_image_width int,
> info_href_read_image int,
> info_href_read_image_height int,
> info_href_read_image_width int,
> metric_scope nvarchar(1),
> metric_type_image int,
> metric_type_image_height int,
> metric_type_image_width int,
> metric_type_image_tooltip nvarchar(60),
> range_type nvarchar(1),
> metric_desc_tooltip nvarchar(60)
> )
> insert into @advisor
> (
> group_id,
> group_desc,
> group_type,
> display_column,
> display_row,
> info_text,
> info_href,
> group_image,
> image_height,
> image_width,
> metric_id,
> metric_desc,
> sum_metric_value,
> metric_format,
> am_display_row,
> am_display_column,
> link_href,metric_type,
> info_href_unread_image,
> info_href_unread_image_height,
> info_href_unread_image_width,
> info_href_read_image,
> info_href_read_image_height,
> info_href_read_image_width,
> metric_scope,metric_type_image,
> metric_type_image_height,
> metric_type_image_width,
> metric_type_image_tooltip,
> range_type,
> metric_desc_tooltip
> )
> exec dbo.sv_ng_advisor '0060', null, '2643', null
> select * from @advisor
> GO
>
> What am i doing wrong? Is there an easier way to export the rows returned
> by the stored procedure? Why doesn't my table variable return any rows even
> though it runs fine in query analyzer? Why isn't the SSIS import/export
> wizard smart enough to realize i put a temp table in the query?
>
> Is there some trick to exporting data from a stored proc that uses the FOR
> XML RAW format into a flat file or whatever other file is doable?
>
> I'm totally disappointed w/ SSIS. I don't understand why everyone says its
> so much better than DTS was. I have had nothing but trouble trying even to
> use the wizards from it. I never had any issues using DTS in the past on
> 2000.
>
> In adddition, for having all this wonderful XML integration, SQL 2005 sure
> doesn't seem to provide much for exporting out of the box. How integrated is
> that?