Need an alternative to SQL2000 DTS packages

aimeec
aimeec used Ask the Experts™
on
Hello Experts
I get 25 Fixed length field Text file extracts from our production system, which I then import into an SQL server database for our CRM and MIS systems. This is currently done with the DTS package system from SQL server 2000 which is then exported into a VB6 program.

My Managers have been on selling my system to other companies, who then require me to modify it to their requirements. I inherited this system from my predecessor, and it was never designed to have the potential to be modified to the extent the other companies want. So these modifications take way longer than they should.

I am looking for a replacement which is impressive, scalable, easy to maintain/modify, and one that I can present to the other companies IT departments so they are willing to take over their own maintenance after the initial modifications.

Also, our company is probably going to be taken over in October(pending shareholder votes) and I want to have something that will show that I have some sort of skills and that Im not just a Uni student looking after a legacy system.

The main things my current system does, that I want the new system to do better:
- The text files have a Date extension (MIS001.20090722), my system uses this date for the snapshot that its loading. Because the Text file names are not static, I have to modify the VB output from the DTS to handle this. There is a possibility to change the text file names to static ones (MIS001.txt), not straight forward, but possible.
- The default blank date is 99/99/9999, I use the VBScript feature to see if the text = that, and I change it to 31/12/9999 to store it in a date field.
- I would like an easy way to change the database that it is pointing to. So I can go between Test_DB and Prod_DB without having to go through the code and change all the references to a variable. Also, so I can clone the program and point it to anotherCompany_DB for onselling.
- There is some data that gets put into staging-tables and then its retrieved with views, because the data comes from several of the files.
- Ability to export to an executable would be a bonus for the smaller companies where we continue to do their maintenance
- It is automated daily and is triggered by the text files arriving after being ftp'd from the host.
- some of the data gets modified.

The new system needs to be free, or included with SQL server 2000 or 2005 (SSIS?) so we can sell/give it to the other companies to do their own maintenance. Writing it in .Net is a possibility as well (except I have no Idea how to go about DB concurrency and transaction control).

I have tried to research this a bit, but I'm not sure what to ask Google. So i keep getting irrelevant results.

So, If you're still with me after all my blabbing on, I'm open to suggestions.

Thanks
Aimee
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
>>Because the Text file names are not static, I have to modify the VB output from the DTS to handle this. <<
Declare a Global Variable.
Set the connection to the text file dynamically using the Global Variable.
Pass in the value to the DTS Package to set the value for the Global Variable.

>>The default blank date is 99/99/9999, I use the VBScript feature to see if the text = that, and I change it to 31/12/9999 to store it in a date field<<
A better approach is to set it to Null.  But whatever works for you.

>>I would like an easy way to change the database that it is pointing to. <<
See my first point.
Hemantgiri S. GoswamiModerator - MSDN Forums
Commented:
==> Writing it in .Net is a possibility as well (except I have no Idea how to go about DB concurrency and transaction control).
-- This would be good, for transactions/concurrencies, go through the article here  http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx this will give you an idea about different Transaction Isolation Levels and how to use them
hth
Hemantgiri S. Goswami

Author

Commented:
Thanks for your replies.

acperkins: For the Global Vars for Text file and DB connections: Is there a way to do this in the DTS package designer, before exporting to VB?
I am already doing this in the code after exporting to VB. The problem I have with the way I am doing this, is that the connections are hard coded all the way through the file. and for the Text files, the file Path is also hardcoded.
Eg:
oCustomTask7.SourceObjectName = gExtractFileLocation & "MIS015" & sExt   '**Was "C:\ExtractFiles\DTS\MIS015.20090721"
oCustomTask7.DestinationConnectionID = 1
oCustomTask7.DestinationObjectName = "[" & gSQLInitCatalog & "].[dbo].[tblMemberID]"  '** catalog is the DB Name, which changes for Prod, and test

The text file ones arent too bad because they are only used once, but the database connections are used heaps of times, so you have to make heaps of changes throughout the exported VB File.


ghemant: Nice article.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012
Commented:
>>Is there a way to do this in the DTS package designer, before exporting to VB? <<
Sure there is. See my previous comment.  The data source can be set using the Dynamic Properties Task or alternatively using an ActiveX Script Task.

Author

Commented:
-->The data source can be set using the Dynamic Properties Task.
I wish I knew about that a year and a half ago. It's going to save me heaps of time.

Thanks

Author

Commented:
Thanks. I split the points because the Dynamic Properties task is going to solve this short term. But I think I will write this in .net sometime soon and that article will really help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial