Using SQLDMO in .net (VB.net)
Using SQLDMO within .net isn't the easiest thing to find information about, so here goes with some documentation.
The first thing that you need to do is add a reference to the SQLDMO library. With .net, you do this by: (when within a project)
1 - Choose "Project" from the menubar and then "Add Reference"
2 - In the dialog that appears, choose the "COM" tab
3 - Choose (double click) the entry named "Microsoft SQLDMO Object Library", it will then appear in the listbox at the bottom of the dialog. ("Selected Components")
4 - Click OK
Note: If the SQLDMO entry is not present, then you likely don't have SQLDMO installed on your machine, or if you do its COM registration is in some way corrupt. Try re-installing.
In the "References" node of Solution Explorer, there will now be a new entry called "Interop.SQLDMO". You're all set now to start using SQLDMO in your code. It's worth noting that SQLDMO seems to have problems if you put an "Imports SQLDMO" directive in your code, so don't do it.
Thanks man! I was wondering how to set it up in my project!
I have a problem with the way this works.
When you add a refrence to the SQLDMO.dll in the binn directory, there seems to be a reference to this path.
What happens if you deploy an application to another computer where the SQLDMO is on the D drive instead of the C drive?
Thanks for the comment, I'll address this in more detail in another blog entry, but, the crux of it is:
1 - SQLDMO.dll is a COM DLL, thus it is loaded via the registry so, the interop wrapper that .net produces shouldn't have any problems finding it no matter where it's located.
((As it is part of the SQL Server Client Tools, I'm not sure what the re-distribution rights / licensing are for SQLDMO.dll so I would advise checking prior to actually re-distributing the SQLDMO DLL's separately.))
I have to be honest and say that I'm not an expert on the P&D process with .net, as far as I'm concerned it generally just works so I've never really looked at it much. I'm not sure what it does with Interop Assemblies, but I'd assume it does pick them up so there shouldn’t be any deployment problems!
Note... Doing this within a "script task" in SSIS is not possible since SSIS script tasks do not allow references to COM objects.
I am having the same problem, so I added the sqldmo.dll now its telling me that...
Cannot create ActiveX component...
Object Reference not set to an instance to an instance of an object.
what should i do to rectify this problem..