December 5, 2006 at 11:56 am
I'm looking for some advice on how i can use dts to execute a stored proc and send the results to an excel report.
environment: sql 2000, developer edition, sp3, excel 2003.
My store proc gathers info on successful data loads from about 10 different databases and uses various inner join commands to collect this.
When i send it to a text file, no problem. I have four columns, keystring, valuestring, redstring, and greenstring.
But when I try to set this up in a dts package...failure.
I hope this is the right discussion group and if not, I apologize.
any help would be greatly appreciated.
December 5, 2006 at 12:15 pm
I don't see any problem doing this.
what error you are getting?
MohammedU
Microsoft SQL Server MVP
December 5, 2006 at 12:23 pm
Invalid Pointer.
I went ahead and created a blank spreadsheat with the four columns and then pointed the excel object to this file. Under the 'Destination Tab' I created a new table, ETL_Report, same name as the excel file and then set up the columns and data types.
Under transformations I have the four columns mapped from the source to the destination, column to column. But I can't seem to get this to work.
December 5, 2006 at 12:35 pm
Sorry! May be I am missing something here...
I have created a test procedure and exported into an excel without any issue. In excel will append the data if you run twice so that you have drop the table in excel or you have to use template for this issue...
You are saying it is not working but you didn't mention any error...
Can you be more specific about the error.
MohammedU
Microsoft SQL Server MVP
December 5, 2006 at 3:23 pm
pretty much the only message is 'invalid pointer'. i can assume from this that my column mapping is wrong. but when i checked under the transformation tab, the columns are set for column copy and each column is mapped one to one from source to destination:
keystring to ketstring
valuestring to valuestring
redstring to redstring
greenstring to greenstring
December 5, 2006 at 3:55 pm
You can try the alternate solution...
Insert your procedure data into a table in tempdb in dts package using EXECUTE SQL task and then copy the data from the table to excel and then delete the table...
1. Create a table in tempdb...
2. Insert into table tempdb.dbo.tablename Exec procedureName
3. Copy the data from tempdb.dbo.teableName to Excel
4. Drop table tempdb.dbo.tablename
MohammedU
Microsoft SQL Server MVP
December 6, 2006 at 9:06 am
Stored Procedure output to Excel
================================
Step 1
======
DDL and DML
===========
if exists (select * from sysobjects where name = 't1')
drop table t1
create table t1(f1 int, f2 datetime)
insert into t1 values(111,getdate()-10)
insert into t1 values(222,getdate()-11)
insert into t1 values(333,getdate()-12)
insert into t1 values(444,getdate()-13)
insert into t1 values(555,getdate()-14)
insert into t1 values(666,getdate()-10)
insert into t1 values(222,getdate()-12)
insert into t1 values(222,getdate()-13)
insert into t1 values(555,getdate()-15)
insert into t1 values(555,getdate()-16)
insert into t1 values(555,getdate()-17)
create procedure get_t1
as
select * from t1
Step 2
======
Add a "Microsoft OLE DB Provider for SQL Server"
Microsoft "Excel 97-2000" connection
Connect tem using a "Transform Data Task"
Step 3
======
In the 'Source' tab select 'SQL query radio button and enter 'get_t1' without the quotes
On the Destination tab click on 'Create' and click on ok
In the 'Transformations' tab delete all existing transformations and click on 'New'
Select 'ActiveX' from the list
Select all 'Source columns' and 'Destination Columns'
and this should be the code, when you click on Properties
Function Main()
DTSDestination("f2") = DTSSource("f2")
DTSDestination("f1") = DTSSource("f1")
Main = DTSTransformStat_OK
End Function
Step 4
======
That's it, go ahead and execute the package!
December 6, 2006 at 3:46 pm
Hey thanks for detail on this, appreciate it. I've tried other simple tests and no problem...I can get the data I'm looking for formatted into excel. I just can't get this stored proc to perform likewise.
I'm kind of wigged out from this, so any other ideas would be greatly appreciated.
thanks.
December 11, 2006 at 10:08 am
in looking further into this stored proc, it looks like it declares some type of table in memory, then proceeds to gather info from permanent tables and inserts this data into the memory table. at the end there's a select statement against this table '
SELECT * FROM @DataTable'
here's an example of the stroed proc:
DECLARE @DataTable Table(KeyString varchar(100), ValueString varchar(255), RedString varchar(200), GreenString varchar(200))
DECLARE @redflag Int, @GreenFlag Int, @RedString varchar(200), @GreenString varchar(200)
SET @redflag = 0
SET @GreenFlag = 0
SET @RedString = ''
SET @GreenString = ''
--PRINT BMS CUSTOMER
INSERT into @DataTable Values('BMS CUSTOMER','','','')
--get File Name
DECLARE @FILE_ID1 INT
DECLARE @FILE_NAME VARCHAR(255)
SELECT @FILE_ID1 = File_Id FROM db1.dbo.FL_tbl_Customer_Raw (NOLOCK) WHERE Id = 1
SELECT @FILE_NAME = FILE_NAME FROM db2.DBO.M_FILE (NOLOCK) WHERE FILE_ID = @FILE_ID1
INSERT into @DataTable Values('File Name' ,@FILE_NAME,'','')
--get Load Date
DECLARE @LOAD_DATE DATETIME
DECLARE @CREATE_DATE DATETIME
DECLARE @PROCESS_DATE DATETIME
SELECT @LOAD_DATE = LOAD_DATE from db1.dbo.Fl_tbl_Customer_Raw (NOLOCK) where Id = 1
--INSERT into @DataTable Values('Load Date',@LOAD_DATE,'','')
SELECT @CREATE_DATE = Date_Created FROM db2.DBO.M_FILE (NOLOCK) WHERE FILE_ID = @FILE_ID1
SELECT @PROCESS_DATE = Date_Processed FROM db2.DBO.M_FILE (NOLOCK) WHERE FILE_ID = @FILE_ID1
INSERT into @DataTable Values('File Created Date',@CREATE_DATE,'','')
INSERT into @DataTable Values('File Processed Date',@PROCESS_DATE,'','')
-- Raw, History and Corresponding Exceptions
DECLARE @RawCount Int, @HistCount Int, @HistExcp Int
SELECT @RawCount = COUNT(*) FROM db1.dbo.Fl_tbl_Customer_Raw (NOLOCK) WHERE LOAD_DATE=@LOAD_DATE AND BU_BRANCH_ID = 3
SELECT @HistCount = COUNT(*) FROM db1.dbo.Fl_tbl_Customer (NOLOCK) WHERE LOAD_DATE=@LOAD_DATE AND BU_BRANCH_ID = 3
SELECT @HistExcp = COUNT(*) FROM db1.dbo.Fl_tbl_Customer_Exception (NOLOCK) WHERE LOAD_DATE=@LOAD_DATE AND BU_BRANCH_ID = 3
INSERT into @DataTable Values('Records in Raw Table',@RawCount,@RedString,@GreenString)
INSERT into @DataTable Values('Records in History Table',@HistCount,@RedString,@GreenString)
INSERT into @DataTable Values('Exceptions Raw -> History',@HistExcp,@RedString,@GreenString)
DECLARE @FILE_ID INT
SELECT @FILE_ID = File_Id FROM db1.dbo.Fl_tbl_Customer (NOLOCK) WHERE LOAD_DATE=@LOAD_DATE AND BU_BRANCH_ID = 3
IF @FILE_ID IS NULL
BEGIN
SELECT @FILE_ID = File_Id FROM db1.dbo.Fl_tbl_Customer_Exception (NOLOCK) WHERE LOAD_DATE=@LOAD_DATE AND BU_BRANCH_ID = 3
END
-- Total record and exception from History to Target
DECLARE @TotalRec Int, @Exception Int
SELECT @TotalRec = Total_Record FROM db2.dbo.M_File (NOLOCK) where File_Id = @FILE_ID
SELECT @Exception = COUNT(DISTINCT(Column_Value)) FROM db2.dbo.M_LOAD_DATE_EXCEPTION (NOLOCK) WHERE LOAD_DATE = @LOAD_DATE AND BU_BRANCH_ID = 3
IF @TotalRec < (@HistCount + @HistExcp)
BEGIN
SET @redflag = 1
SET @RedString = 'Total records M-File do not match.'
END
INSERT into @DataTable Values('Total Records in M_File Table',@TotalRec,@RedString,@GreenString)
SET @redflag = 0
SET @RedString = ''
-- Check if Exceptions are valid
IF @Exception 0
BEGIN
SET @redflag = 1
SET @RedString = 'Validity of all Exceptions could not be verified. Please check.'
END
INSERT into @DataTable Values('Exceptions History -> Target',@Exception,@RedString,@GreenString)
SET @redflag = 0
SET @RedString = ''
-- Stage and Target on Bu_Customer Table
DECLARE @StageCount Int, @TargCount Int, @StageCust Int
SELECT @StageCust = COUNT(*) FROM db3.dbo.BU_CUSTOMER (NOLOCK) WHERE LOAD_DATE = @LOAD_DATE AND BU_BRANCHID = 3
SELECT @TargCount = COUNT(*) FROM db4.dbo.BU_CUSTOMER (NOLOCK) WHERE LOAD_DATE = @LOAD_DATE AND BU_BRANCHID = 3
IF @HistCount (@TargCount+ @Exception)
BEGIN
SET @redflag = 1
SET @RedString = 'Total number of records loaded in Target/Stage and the corresponding exceptions do not match with History Count.'
END
INSERT into @DataTable Values('Records in Bu_Customer - Stage',@StageCust,@RedString,@GreenString)
SET @redflag = 0
SET @RedString = ''
DECLARE @TargExcp int
SELECT @TargExcp = COUNT(DISTINCT(Column_Value)) FROM db2.dbo.M_Load_Date_Exception (NOLOCK) WHERE LOAD_DATE = @LOAD_DATE AND LOCATION_ID = 5
IF @StageCust (@TargCount + @TargExcp)
BEGIN
SET @redflag = 1
SET @RedString = 'Total number of records loaded in Stage and that in Target is not equal.'
END
INSERT into @DataTable Values('Records in Bu_Customer - Target',@TargCount,@RedString,@GreenString)
SET @redflag = 0
SET @RedString = ''
--Stage and Target in Bu_Customer_AddressInfo Table
SELECT @StageCount = COUNT(*) FROM db3.dbo.BU_CUSTOMER_ADDRESSINFO A (NOLOCK)
INNER JOIN db3.dbo.BU_CUSTOMER B (NOLOCK) ON B.BU_CUSTOMER_ID=A.BU_CUSTOMER_ID
WHERE B.BU_BRANCHID=3 AND B.LOAD_DATE=@LOAD_DATE AND BU_BRANCHID = 3
SELECT @TargCount = COUNT(*) FROM db4.dbo.BU_CUSTOMER_ADDRESSINFO A (NOLOCK)
INNER JOIN db4.dbo.BU_CUSTOMER B (NOLOCK) ON B.BU_CUSTOMER_ID=A.BU_CUSTOMER_ID
WHERE B.BU_BRANCHID=3 AND B.LOAD_DATE=@LOAD_DATE AND BU_BRANCHID = 3
AND A.CHANGE_DATE>@LOAD_DATE
INSERT into @DataTable Values('Records in Bu_Customer_Addressinfo - Stage',@StageCount,@RedString,@GreenString)
IF @StageCount (@TargCount)
BEGIN
SET @redflag = 1
SET @RedString = 'Total number of records loaded in Stage and that in Target is not equal.'
END
INSERT into @DataTable Values('Records in Bu_Customer_Addressinfo - Target',@TargCount,@RedString,@GreenString)
SET @redflag = 0
SET @RedString = ''
December 11, 2006 at 10:37 am
Or to save your brain a lot of work if you have Reporting Services installed you can get RS to do this much easier for you thru Subscriptions. I have done both ways and found littls issues here and there with DTS but RS has had few so far other than column borders disappear.
December 11, 2006 at 11:13 am
Hey thanks for the idea SSC. In this environment they have sql 2000 with the main db's and then their using sql 2005 reporting services for their reports.
The stored proc is stored in one of the sql 2000 databases and I'm assuming sql 2005 reporting service wouldn't have a problem with the backward compatibility.
December 11, 2006 at 12:13 pm
No there are no issues at all. You can even setup email delivery, and store historical snapshots in case you need to retrieve any past runs.
December 11, 2006 at 12:50 pm
So, I should be able to call this store procedure and have reporting services format the result set into an excel format?
December 11, 2006 at 1:27 pm
Yes, you will have to build a report to format the layout like you want but if you have access to the RS Console go into an existing report and click on the report to review. Once visible you will see a tab option "Subscriptions" on that tab click the "New Subscriptions" button and you will see the available options. To put the file out to a location change the "Delivered by" method to "Report Server File Share" and you will see all the formats you can use. You build the subscription and it will create a job to run it on a regular basis. You can also create multiple subscriptions if you need to create files based on criteria to the Stored Procedure.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply