DTS Export to Excel

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

ThanksPost the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||Here goes the Stored Procedure

CREATE PROCEDURE sp_ProductivityReport_ByMonth

@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

DECLARE @.@.total1 decimal(20,2)
DECLARE @.@.total2 decimal(20,2)
set @.@.total1 = 0
set @.@.total2 = 0

select B.T_FName as "First_Name", B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"

into #CustomTable1
from dbo.BillingInfo A (nolock), dbo.TInfo B (nolock)
where
A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY
A.T_ID, B.T_FName, B.T_LName

DECLARE Total_Cursor CURSOR Local
FOR SELECT Lines_Changed FROM #CustomTable1
OPEN Total_Cursor
FETCH next From Total_Cursor
INTO @.@.total2
while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor
INTO @.@.total2
END
CLOSE Total_Cursor
DEALLOCATE Total_Cursor

/**** Calculating Total*******/
DECLARE @.Reports_Total int
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1

DECLARE @.T_M_Total decimal(15,2)
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1

select "xxx - Total" as First_Name, ' ' as Last_Name, @.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed, @.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2

set nocount on

SELECT First_Name, Last_Name,Reports_Changed,
Minutes_Changed, Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.@.total1) AS decimal(5,2))) + '%'
as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT * FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

Post the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||You really need to learn more about TSQL, and principles of database application design before attempting something like this. Why? Because you are taking the wrong approach to solving a problem that, really, you should not be trying to solve with SQL in the first place.

Lets begin...

Dump the cursor and learn how to write set-based logic. This entire sectionDECLARE @.@.total2 decimal(20,2)
set @.@.total2 = 0
.
.
.
DECLARE Total_Cursor CURSOR Local FOR
SELECT Lines_Changed
FROM #CustomTable1

OPEN Total_Cursor
FETCH next From Total_Cursor INTO @.@.total2

while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor INTO @.@.total2
END

CLOSE Total_Cursor
DEALLOCATE Total_Cursor...can be replace by one line:SET @.@.totall = sum(Lines_Changed) from #CustomTable1

Next, all of these lines and more...SET @.@.totall = sum(Line_Changed) from #CustomTable1
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1
.
.
.
select "xxx - Total" as First_Name,
' ' as Last_Name,
@.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed,
@.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
...can be run as a single statementselect "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

Then, I have to ask, what is with the use of the double ampersands?

Lastly, it poor programming practice to be calculating subtotals to a report within SQL, as you are doing with CustomTable2. This is best handled by whatever reporting application you are using. Not because of a limitation within SQL, but because you are essentially mixing record types (raw and total) within a single dataset. Bad form.

So, re-read the Books Online sections on SELECT statements and aggregate queries. And if you find yourself using cursors again, be confident you are doing something wrong because you probably are.

But for the heck of it, go ahead and try this shortened code:CREATE PROCEDURE sp_ProductivityReport_ByMonth
@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

select B.T_FName as "First_Name",
B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"
into #CustomTable1
from dbo.BillingInfo A (nolock),
dbo.TInfo B (nolock)
where A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY A.T_ID,
B.T_FName,
B.T_LName

/**** Calculating Total*******/
select "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

SELECT First_Name,
Last_Name,
Reports_Changed,
Minutes_Changed,
Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.total1) AS decimal(5,2))) + '%' as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT *
FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

DTS / DMO

I'd like to load a few huge CVS files to an MSSQL server.
I don't want to create temporary files, instead I want to read the CVS
file from the disk line by line and after transforming it I'd send it to
the database.
Formerly I used INSERTs, but they seemed very slow.
BULK INSERTs don't work either, because the file format cannot be parsed
with bcp.
Now I'm considering using DTS or DMO, but I don't know which of them
fits my needs better. And which of them is the easier to learn and work
with from C#.
I'd be greatful for any example, or comment on using these tools.
Thanks in advance,
Gabor GludovatzDTS is definatley the way to go to here. You can use it to do
transforms if the CSV format needs to be massaged in anyway. You can
also dynamically adjust the columns that might need to be imported if
you have different file formats.
Gabor Gludovatz wrote:
> I'd like to load a few huge CVS files to an MSSQL server.
> I don't want to create temporary files, instead I want to read the CVS
> file from the disk line by line and after transforming it I'd send it to
> the database.
> Formerly I used INSERTs, but they seemed very slow.
> BULK INSERTs don't work either, because the file format cannot be parsed
> with bcp.
> Now I'm considering using DTS or DMO, but I don't know which of them
> fits my needs better. And which of them is the easier to learn and work
> with from C#.
> I'd be greatful for any example, or comment on using these tools.
> Thanks in advance,
> Gabor Gludovatz
>