DTS: Export to Excel

25 pts.
Tags:
ActiveX
DTS
DTS Import/Export Wizard
Microsoft Excel
I'm exporting a simple table to Excel but my problem is that the table columns will change every month since the column names will list month names (ex. May2009). Can I use ActiveX to dynamically create the transformation source? Please help!

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello

You can try using the transformation between SQL server to MS Excel. If this doesn’t work

than use ActiveX script to generate the MS Excel file which will able to handle dynamic column names.

This will involve writing macro which will select the required columns from table in SQL and put the same in MS Excel file and save the same with new name each time.

This macro will be called from Active X script which will run through DTS.

If you need further help, please do write. I will let u the activeX scripts to run the macro.

Regards,
Sarfaraz Ahmed

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Stan1001
    Thanks for suggestions. I have used Active X to dynamically write the new column names into the Excel file. My problem is in the transformation for the Source table. This table has new columns every month so next month when I run it, I will have to re-do the transformation (source table) for the new column names. I don't want to do this every month so how do I dynamically create the transformation so that it sees the new columns in the table? Thanks!
    25 pointsBadges:
    report
  • Stan1001
    Figured out a solution... The number of columns in the table will remain the same so I can copy the data to a temp table that will have standard column names and use this table in the transformation.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following