DTS loop to send each customer''s data into a separate Excel file

Question

DTS loop to send each customer''s data into a separate Excel file

smv929

SSCrazy

Points: 2889
More actions
October 18, 2005 at 2:52 pm

#76440

I have a TSQL statement script that performs a query and saves each customer's sales data to a csv file. It works fine and quick. However, it's intended to be opened in Excel. THe problem is that formatting lacks something to be disired (i.e., column widths are too narrow and leading zeros in order numbers are missing, etc.) I figure DTS with an ActiveX to create the Excel files would a way to generate better looking customer sales reports. I'm new to ActiveX/VB Scripting and looping and can't find good examples (i've looked at sqldts). As I said, the big challenge (to me, being a ActiveX task newbie) is the looping part.
The basic steps would be...
1. Get a list of customer numbers that will recieve the report. Only certain customers get them. These are stored in a small table.
2. Excecute a sales query WHERE CustNum IN (<the table of customers> ) to populate a temp table containing sales results for all customers.
3. Query the temp table to send the sales results for each customer into a separate excel file, saving each filename as custnum_date.xls.
Any help would be greatly appreciated. Thanks!

smv929

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MarkG Ten Centuries Points: 1237 More actions · Answer 1

This isn't an exact solution.. but maybe you can adapt this for your situation. I don't know how to loop it in a DTS pkg, but you could call this proc from inside a TSQL proc. I didn't write it, but i did adapt (read "hack") it for my own purposes - along with some notes on gotcha's:

CREATE PROC dbo.ap_ExcelReport

@FileName varchar(25) -- Base name of the file.. will have the DateStamp appended to this name

,@SQL varchar(8000) -- sql select statement.. has some limitations, no ORDER BY or DISTINCT for some datatypes

,@WKS_Name varchar(100) -- Name of the Spreadsheet tab..

,@DDL varchar(500) -- Column Names in Excel and Excel data type

,@FilePath varchar(250) -- UNC path name of target location

AS

/*****************************************************************************************************

PROCEDURE: adapted from HPSP_UT_ExcelReport Original Proc by: Leah Kats

http://www.sqlservercentral.com/scripts/contributions/1507.asp

Created Date: 05/25/2005 This utility stored

12-SEP-2005 Mark Gelatt, Sr.

Added code to check for and delete an existing spreadsheet

or we will keep appending data to the same spreadsheet over and over

if you execute it again on the same day - courtesy of Susan Knowles - mssql maillist

changed the name of the proc to comply with naming standards..

Also included some comments and sample syntax (if you have PUBS installed)

EXAMPLE SYNTAX:

exec ap_ExcelReport 'NameList',

'SELECT SSN = au_id, Name = RTRIM(LTRIM(RTRIM(ISNULL(au_fname,'''') + '' '' + ISNULL(au_lname,'''')))), Phone FROM PUBS.dbo.Authors',

'Phones',

'SSN Text, Name Text, Phone Text',

'\\YourServerName\D$\DBA\'

NOTE: If you reference any private share (C$, D$) you must be part of the local Administrator's group

to reference these drives.. otherwise create a Share and just reference the share name without

the D$ or C$. '\\YourServerName\ShareName\DBA\'

I believe you need to have EXCEL installed on your server for this to work.

Remember, if you schedule this proc in a Job, the Account that SQL Server Agent runs under

needs to have rights to the target folder - check with your network admin.

You could probably re-write this proc to accept the name of a proc instead of a sql string

for the @sql variable

*****************************************************************************************************/

SET NOCOUNT ON

DECLARE @Conn int -- ADO Connection object to create XLS

, @hr int -- OLE return value

, @src varchar(255) -- OLE Error Source

, @desc varchar(255) -- OLE Error Description

, @Path varchar(255) -- Drive or UNC path for XLS

, @connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM

, @WKS_Created bit -- Whether the XLS Worksheet exists

, @xlsDDL varchar(1000)

, @LocalDDL varchar(1000)

, @ServerName nvarchar(128) -- Linked Server name for XLS

, @Recs int -- Number of records added to XLS

, @Log bit -- Whether to log process detail

-- Init variables

SELECT @Recs = 0

, @Log = 1

-------------------------------------------------------------------------------------------------------

-- assign a path and name for the XLS file

-------------------------------------------------------------------------------------------------------

SET @Path = @FilePath + @FileName + CONVERT(varchar(10),GETDATE(),112)+'.xls'

-------------------------------------------------------------------------------------------------------

-- assign the ADO connection string for the XLS creation

-------------------------------------------------------------------------------------------------------

SET @connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'

-------------------------------------------------------------------------------------------------------

-- assign the Linked Server name for the XLS population

-------------------------------------------------------------------------------------------------------

SET @ServerName = 'EXCEL_TEST'

SET @xlsDDL = 'CREATE TABLE '+@WKS_Name+ ' (' + @DDL + ')'

SET @LocalDDL = REPLACE(@DDL, 'text', ' ')

SET @sql = 'INSERT INTO '+@ServerName+'...'+@WKS_Name + ' (' + @LocalDDL + ') ' + @sql

-- Be sure we DELETE the existing file first..

--Create temporary table for xp_fileexists result set- Delete code courtesy of Susan Knowles - mssql maillist

CREATE TABLE #FileExists

( DoesExist smallint,

FileInDir smallint,

DirExist smallint)

DECLARE @DelCmd varchar(128)

SET @DelCmd = 'DEL ' + @path -- @path no includes the filename and extension of .XLS

BEGIN

INSERT INTO #FileExists EXEC Master..xp_FileExist @Path

IF EXISTS (SELECT DoesExist FROM #FileExists fe WHERE fe.DoesExist = 1)

BEGIN

EXEC Master..xp_cmdshell @DelCmd, no_output

Print 'Deleting existing spreadsheet...'

END

DROP TABLE #FileExists

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'

-- Create the Conn object

EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT

IF @hr <> 0

BEGIN

-- Return OLE error

EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

EXEC @hr = sp_OADestroy @Conn

RETURN

END

IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'

-- Set a the Conn object's ConnectionString property

-- Work-around for error using a variable parameter on the Open method

EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @connect

IF @hr <> 0

BEGIN

-- Return OLE error

EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'

-- Call the Open method to create the XLS if it does not exist, can't use parameters

EXEC @hr = sp_OAMethod @Conn , 'Open'

IF @hr <> 0

BEGIN

-- Return OLE error

EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @xlsDDL, NULL, 129 -- adCmdText + adExecuteNoRecords

IF @hr = 0x80040E14

BEGIN

PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'

SET @WKS_Created = 0

END

BEGIN

SET @hr = 0 -- ignore these errors

END

IF @hr <> 0

BEGIN

-- Return OLE error

EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'

--!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!

EXEC @hr = sp_OADestroy @Conn

IF @hr <> 0

BEGIN

-- Return OLE error

EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT

SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)

BEGIN

IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'

EXEC sp_addlinkedserver @server = @ServerName

, @srvproduct = 'Microsoft Excel Workbook'

, @provider = 'Microsoft.Jet.OLEDB.4.0'

, @datasrc = @Path

, @provstr = 'Excel 8.0'

EXEC sp_addlinkedsrvlogin @ServerName, 'false'

END

EXEC (@SQL)

PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'

IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)

BEGIN

IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'

EXEC sp_dropserver @ServerName, 'droplogins'

END

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

smv929 SSCrazy Points: 2889 More actions · Answer 2

Thanks much for the script, especially your version. I could easily do a TSQL WHILE loop to have it create each file. However, my main concern is to format the excel files which you suppossedly can do using sp_oacreate or whatever procs to set formatting properties.

That's why I was going to use the ActiveX DTS task. I basically want to set the Autofit property (for column widths) and freeze the row of titles at the top.

Anyone know how to do this with the sp_oa procs?

smv929