Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Solution to Execute DTS Packages and Connect to SQL Server with Vb.net

Posted on 2004-10-19
7
1,100 Views
Last Modified: 2009-05-27
I am trying to create a solution, that allows a user to select a file and have that file imported into sql server, i believe that i can do this with a .net and dts solution, creating my dts packages, and allowing the user to choose where the files are, ftp them from a server, and put them in a pre determined destination so that a dts package can execute the data pump, i need to assume that all the text files are the same every time we run the conversion, and i can run the dts with the same structure. i am new to .net, i have done something similar using vb6, but it's been a whlie, and .net is very new to me. so i need to know how to connect to a database, using ado.net ? i presume, and how to execute the dts pacages, and how to allow the user to select where the files are. i just need a kick in the right direction so that i can get started, i'm sure that i will tweak it once this get up and running. thanks
0
Comment
Question by:basile
  • 3
7 Comments
 
LVL 6

Accepted Solution

by:
etmendz earned 500 total points
ID: 12356218
In your VB.Net project, add a COM reference to the Microsoft DTSPackage Object Library 2.0 which is \Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll. This action will create Interop.DTS.dll and should show in your Reference list as DTS.

I used the information in the following link to help me:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321525

The sample code in this article simply calls an existing DTS package in an SQL Server.

If you want dynamic DTS from inside VB.Net, now that's a different story. I've done this before by creating a .Net application (it was my first practical attempt to learning VB.Net). If you want to do dynamic DTS, consider the following:

- Save your DTS package into a VB6 code.
- It is important that you understand the DTS VB6 code so you can easily covert it to VB.Net.
- Base your VB.Net code on the generated VB6 code.
- Expect your VB.Net code to more or less look like the original VB6 code.
- In your VB.Net code, add the code for the PackageEventSink as defined in the link I gave you.

The basic process flow is:

Initialize DTSPackage Events Sink
Initialize yourDTSPackageObject
Initialize yourDTSPackageObject Connections
Define yourDTSPackageObject Steps
Define yourDTSPackageObject Tasks
yourDTSPackageObject.Execute()
yourDTSPackageObject.UnInitialize()
UnInitialize DTS Package Events Sink

More or less, you should find the same pattern in the DTS VB6 code. :-)

Have fun.
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12356409
For the other stuffs you need, those are basic I/O operations and you can simply search msdn.microsoft.com for ".Net System.IO" for more information. The classes that may also interest are the OpenFileDialog and the FileSystemWatcher classes. Read about them and about ADO.Net in your Visual Studio.Net documentation, MSDN Library or the MSDN Library online in msdn.microsoft.com.

Samples are available in www.gotdotnet.com where you can view samples organized per class/classes that may interest you.

There is no built-in FTP client in .Net so you might need to download one from the Internet or purchase one from any .Net library vendor you like.

If you've worked with VB6 forms and controls, learning VB.Net forms and controls is a breeze. If you've worked with VB6 .cls class files (which is almost OOP), the transition to VB.Net shouldn't be difficult. If you're familiar with the VB6 IDE, mastering the Visual Studio.Net IDE should be easy.

Have fun.
0
 
LVL 1

Author Comment

by:basile
ID: 12439336
here is a twist to this question. how can i create and run a dts package within vb ? are there any good examples for this out there ?
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12441303
That is called dynamic DTS which I also tried to explain in my first post.

I think that there are not much samples. You can create a DTS package using the Enterprise Manager and then save the DTS package to VB6 (which is an existing feature of DTS in SQL Server 2000).

The VB6 code generated works and may be compiled and tested immediately.

You can use the VB6 code generated as basis if you want to rewrite it to VB.Net.

Have fun.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 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