DTS and VS.Net upgrades don't play well!

First off, thanks to Rob for setting me up with a blog over here at weblogs.asp.net!

The Replication question I posted late last week has been answered, I think. We are setting up a second database that will hold a 4 tables for each of the remote sites. Then we will have a process running to take changes to the main database, and store those changes for each of the sites into the site's 4 tables in the staging database. There will be process at the site running to get changes from the staging database, apply them to the site's database, and also push any changes from the local database to the staging database to be grabbed and put back into the main database.  It's quite a bit more complex than that, but that's the basic structure of things..

Here's what I am trying to do

One late night last week I created a DTS script that pulls data from the Main database, into a database I created, that has all of the same tables (minus a few) that are not necessary for a property database. I created this with the data import wizard, which would pull all of the database information to the new database, I saved this as a DTS, saved the DTS as a VB (bas) file so that I could then go through it with a text editor and add a few where statements to some of the tables, so that for specific tables it would only pull the data for a Property, not for every property.

I imported this BAS file into a VB6 .exe file so that I could then run the executable and write the DTS back into the SQL Server DTS section. I ran the DTS, made a few changes to the work flow so that the data would pull properly without any FK errors. Fine and dandy you say? I thought so too. The problem so far is that the DTS is hard coded for one location, so what I need to do is get a variable into that DTS so that all the where statements use a variable, instead of a hard coded PropertyCode.

To do this I tried the following. I exported the newly modified DTS to a VB (bas) file, and brought it into a new VB6 .exe project. My intentions were to import this project into VS.Net 2003 so that I could then modify to my hearts content and integrate this data download as part of a windows form application. Well, upgrading the VB6 project into VS2003 didn’t work, it hung for about 2 hours before I stopped the upgrade. I then upgraded it to VS2002, and it worked! But not really, there were 65 tasks in the DTS script, 65 functions in the VB6 project, and now there are 15 in the VS2002 project.

So at this point I am stumped. I am going to try to re-export the DTS to a VB file, and then create a new VB6 project, and then attach the DTS to this project and then try to upgrade that project again. I have tried a few times on the other VB6 project, and each time the upgrade stops with 15 functions, the weird thing is it seems to think it’s complete, and doesn’t report any errors or warnings about not having imported less than half of the program.

I plan to use this blog to discuss projects I am working on, and especially the little things, things that make .NET easier to use, short cuts, and other programming theories and practices.

Now here’s a little about myself. I am an application developer for a small company in St. Louis, Missouri. We develop applications for internal and external use for our business and our customers. I have been developing in .NET for almost a year now, my main focus prior to working with .NET was ASP, with some Perl and other scripting language experience. At one time I was a web developer for The Sporting News, it was a great place to work, but the pay sucked and business wasn’t going so well for them.

I also do quite a bit of work on the side, I race my 03 350Z, and have a few racing websites that I maintain, solo2.org and SCCAForums.com being the primary focus. I am a partner in a racing supply business, SoloPerformance.com. I’ve been doing a lot of development for DNN lately, and plan to keep using it for current and future projects. Thanks for taking the time to read all this, I’d love to know what you think!

One more thing, my prior blogs can be found at thetrashman.com!

UPDATE

I tracked the problem down to an error with the VB exported file from the DTS. Some of the lines of code in this file were not properly separated by line breaks. Once I properly separated those lines (search and replace) the VB6 program imported into VS2003 without any of the problems I had experienced before. I then only had to modify the code to correct for syntactical changes and it's ready to go! So my title was incorrect, the problem wasn't DTS and VB.Net, it was strictly DTS :)

4 Comments

  • Chris:



    Not sure I understand what your problem is here w/DTS? You said that you want to use a variable in the DTS pkg. Have you tried using a DTS Global Variable?

  • Not yet Ben, but what I want to do is run what was the DTS in a Windows Form, not actually through SQL server, but I can't seem to get it back into a windows form to try to run it.



    I am trying to develop an application that creates a Property's database, the smaller database, through a windows form, a user would input the PropertyCode, and the application would do the rest



    Install MSDE locally

    Create a new database of the name of the PropertyCode in MSDE

    Import the data from the main database that corresponds to this Propertycode.



    Sorry if my explanation isn't working so well, trying to simplify it as much as possible without going too far in discussing my project outside the company :)

  • The problem really isn't with DTS, it's more with the VS.NET upgrade wizard I think.

  • I figured out that the problem was on the DTS export to the VB(bas) file. The task.SourceSQLStatement values were getting concatenated incorrectly on a few lines, why this occurred I don't know. I did a massive search and replace on a few things and seperated out the lines. I am no reimporting the VB6 project into VS2003, and it appears to be working so far.



    So, the bad lines caused problems in VS2003 in that it wouldn't go through the upgrade wizard, while those same lines caused VS2002 to finish the upgrade wizard, but only partially import the files.

Comments have been disabled for this content.