Dts

[accessman2]

Hi,

I created DTS package,
I want to transfer (export data) the sql results from SQL Server to Excel worksheets

but, I have a problem.

I double click on the Transform Data Task line, and input multiple T-SQL statements in the sql query section, but when I run it, it doesn't work.

Can the sql query section allow multiple statements?

The statements have these,

declare @name varchar(100)

set @name = 'Amy'

create table dbo.##tmp
(
Students varchar(50),
Total varchar(50)
)

insert into ##tmp
select Students, Sum(Scores) Total,
from members
where
Students = @name
group by Students

select * from ##tmp


And, when we do DTS package, can we create temporary table (##tmp) to store the data, and then export them, and then delete temporary table?
Because, I don't think that DTS package accept temporary table, it will give out error message: invalid object (##tmp).

Please let me know, thanks.

 

[pdx_man]

Well ... first off, I have to ask why you are using variables and temp tables for such a simple process ...

select Students, Sum(Scores) Total,
from members
where Students = 'Amy'
group by Students

Anyhow, you can't use global temp tables in data transformations, which, if you think about it, doesn't make much sense since you would be completing the data transformation immediatly anyway.

Also, I would love it if you could follow-up on these posts with whether or not it corrected your issue. It would be helpful to people searching for similar solutions.