DTS To Create/Export Multiple Excel Files
-
Your guidance please on how to accomplish the following:
I have a server table (tbl_a) containing unique names. For each name, I would like to alter a DTS task (using ActiveX Scripting, I assume) to modify a "Select * From tbl_b Where tbl_b.User = tbl_a.Name", then Export the result set to an Excel file (uniquely named for each user.)
While I've found some supportive documents on how to create a global rowset and loop through it, and have used the Export Wizard to create a "one-time" export, I have not been able to integrate these successfully.
Am I on the right track?
-
I have written packages to do such a thing, but they are not easy to import and require a lot of changes to function on a different server.
The best thing you can do is as follows:
Explore the Dynamic properties task. It allows you to change a dts parameter at runtime. You then need to use a global variable for your where clause. This can also be set in the configuration file.
Set a global variable for the where clause.
Set a global variable for the excel filename.
See this article to get you started. It helped me a lot.
http://www.databasejournal.com/features/mssql/article.php/3073161
Once you have this working, you can write a simple vba app to call the package via a command line. Then you can loop through all the values in the where clause calling the dts package each time.
If you get stuck, come back onto the forum.
------------------------------
The Users are always right - when I'm not wrong! -
Thank you for the response. The article looks good. I'll let you know of my progress.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply