DTS from code

Question

DTS from code

John Gibson-224075

Newbie

Points: 2
More actions
April 11, 2005 at 1:19 pm

#362390

I need to create a ASP.Net site using VB.Net that allows our developers to select aDatabase or a table in a database on our beta server and have that table refreshed from the production server. I know that the quickest way to refresh these table would be to use DTS. The problem is I am a complete dummy when it comes to DTS. I would appriciate any help anyone can offer me as far as how to create a DTS package that will accept variable for the database and table names and will refresh tose objects. I have been researching this for two days now and think thatI should use Global variables and Dynamic Parameters. I have just hit a wall when it comes to the dts package. I think I need two packages. One to refresh an entire database. And one for specific tables in a specific database. Can some one help me with creating these packages and maybe getting them to read their object names dynamically?
thank you very much
JPG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

paulmazur SSC Enthusiast Points: 172 More actions · Answer 1

Create a DTS package that has one task which is the Copy SQL Server objects task. After you fill in the Source and Destination tabs, unselect the create destination objects box since you said you were only interested in data. Unclick Copy All Objects then go into the Select Objects... button. There you probably will want to select any table that is a potential candidate for copying. When you have that all set up, do a Save As... There you will find a Location drop down box. Save it as VB. Now you'll have VB that you can edit to "conditional-ize" the tables you want to transfer.

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

I was faced with this problem and solved it by using global variables in the DTS package and calling the package with a stored proc that passed those variables in to the DTS package. Remember, you can not have a variable as a table name in a stored proc, however you can get around that by creating your SQL statement as a string and then running it via the exec command. Hope this helps. If you need further assistance email me with what you are trying to do and I'll see if I can give you some pointers on the DTS package.

MD

Marvin Dillard
Senior Consultant
Claraview Inc

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

I know that the quickest way to refresh these table would be to use DTS.

I would disagree with that. The quickest way, especially from code, would be to use bulk operations and SQL-DMO objects. Another good alternative would be to use to create XML datagrams and insert records using those.

Don't fall into the trap that DTS is the magic hammer that can fix any SQL problem. A full half of the DTS jobs I've seen on client systems are unnecessary and even wasteful. Most can be accomplished much easier and efficiently just using TSQL in a job, many are easier using VBScript and DMO. Know all the tools and what the strengths and weaknesses are.

Mark

John Gibson-224075 Newbie Points: 2 More actions · Answer 4

Thanks Paul,

This is what i needed to get me pointed in the right direction. This will help alot.