asked on

DTS Export to excel, template file not present

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?

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tacobell777

ASKER

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..

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jay Toops

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SoftEng007

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.

Tacobell777

ASKER

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?

arbert

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