DTS Export to excel, template file not present

Posted on 2004-08-16
Medium Priority
Last Modified: 2013-11-30
I have several DTS packages that export to Excel, you know how you need to create the destination table in the Transform Data Task Properties before you can do an export? I would like to have the DTS package create the destination table and file if a template does not already exist.
How do I go about doing that?
Any sample code?
Question by:Tacobell777
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 34

Assisted Solution

arbert earned 750 total points
ID: 11817508
It's not that easy--does your format stay the same from file to file?  The easiest method we've really found is to create a "template" xls file that just has headings (or whatever is needed, database range, etc).  Then, when the DTS package starts, it copies the blank "template" xls file to the "working" xls file for the output.
LVL 17

Author Comment

ID: 11817634
yeah thats what I am doing at the moment, but that doesn't suit me all the time. Like for example when the DTS is moved to Production I'd like it to create the template if it doesn't exist.

There must be some other way of doing it? This is a pain in the butt..
LVL 34

Assisted Solution

arbert earned 750 total points
ID: 11817708
Couple of other options we've used--use an Execute SQL task with a create table statement against the excel connection--I've had mixed results with this.  If you have Excel installed on the server (most shops don't and wouldn't) you could use the Excel Object Model to create the structure you need on the fly.

End the end, we opted for the "copy the template" method.  Without using the Excel Object Model, this seemed to be the easiest way to get the sheect exaclty like we needed it. :(
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

LVL 10

Accepted Solution

Jay Toops earned 750 total points
ID: 11821594
How about this.

store the template as an blob object in the database.


Expert Comment

ID: 11822955
I also have had mixed results using Execute SQL task with a create table statement against the excel connection.

We also opted for a template copy model.
much more reliable and you can get some realy good looking data exports using a prefab template.
LVL 17

Author Comment

ID: 11825979
jltoops, I like your idea - its probably not one of the quickest solutions, but at least the template is stored with the database.

I guess you could implement some ActiveX task to see if the template exists, if so, then copy it, if not then retrieve it from the db and copy it.

Still: is there no way to look at the Transformations - destination columns and create a template dynamically based on that information?
LVL 34

Expert Comment

ID: 11826585
Just with the executesql task (unless you have excel loaded on the server)....

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question