Generating multiple Excel files using DTS?

Last Post 12 Jun 2006 04:06 AM by mwesch. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Gophermouse
New Member
New Member

--
12 Jun 2006 02:45 AM
Hi All

I have a dts package which generates data based on tables and the idea is that this data is output to Excel files.
However the data is based on locations so each location will have a seperate excel file. So I want to end up will a folder with excel files all named with their location.

I am assumming I need some active X script but I have no idea where to start.

Can anybody help?

Cheers

Gophermouse
mwesch
New Member
New Member

--
12 Jun 2006 04:06 AM
I've dome this in the past:

1. Create an Excel file to be used as a template.
2. In a stored procedure, loop through the list of locations.
3. Inside the location loop, use xp_cmdshell to copy/rename the template file to the desired name.
4. Also inside the location loop, use xp_cmd shell to execute DTS package to export data to file you just created. Pass the filename in as a global variable.
5. In your DTS Package, you should start of with a Dynamic Properties task that changes the Excel connection object to the filename passed in as a global variable.

You may not need the template Excel file in your situation. It was helpful in mince becuase I could apply formatting in advance without a great deal of VBA programming.
You are not authorized to post a reply.

Acceptable Use Policy