Solved

DTS Export to excel, template file not present

Posted on 2004-08-16
7
373 Views
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?
0
Comment
Question by:Tacobell777
7 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
Comment Utility
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.
0
 
LVL 17

Author Comment

by:Tacobell777
Comment Utility
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..
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
Comment Utility
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. :(
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Accepted Solution

by:
Jay Toops earned 250 total points
Comment Utility
How about this.

store the template as an blob object in the database.

Jay
0
 
LVL 9

Expert Comment

by:SoftEng007
Comment Utility
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.
0
 
LVL 17

Author Comment

by:Tacobell777
Comment Utility
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?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Just with the executesql task (unless you have excel loaded on the server)....
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now