We help IT Professionals succeed at work.

ETL Solution?

272 Views
Last Modified: 2013-11-30
Our company is in the process of re-writing our web app and re-designing the supporting database. We will be needing to convert the data in 400+ tables to the new database and I'm unsure as to which method to use.

This is a an application that is sold to clients so this will need to be a process that can be shipped out and ran by the client to convert their data.  A lot of the client will be using only SQL 2000 and others may be running 2005 but using the 2000 compatibility mode.

I would like to be able to do this all in SSIS and would work great for our internal database and 2005 clients, but I'm unaware of a method of using an SSIS package against a 2000 database. I know I can execute other SSIS packages against 2000 from the Business Intelligence Studio, but there are clients that won't have that installed. Is there a reliable way to deloy an SSIS package against a 2000 database this way?

I could use DTS and then require the legacy support be put in for 2005, but honetly I'd so much rather use SSIS for a project this large.

I could also write a whole bunch of scripts and stored procedures. This will get the job done but is just a nightmare to keep track of that much transformation.

Anybody perhaps run into this problem or have any good suggestions?
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That is another option that I thought about. Some will be used 24x7. They can obviously allow for some downtime to do the ugrade, but the less the better. This will be around 100 clients we're talking about as well.  Database sizes anywhere from 500mb to 10g depending on the client.

This is a viable option, though one that will likely encounter a lot of resistance.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Well there is some typical normalization going on during this conversion. Most tables have "user defined fields" (UDFs) in each table allowing costomized fields data to be held in each table.

So for instance

Work order table  (many tables like this all with udfs)
ID       wonumber     date             udf1     udf2      udf3
1         abc123          1/1/2007      Red      123       <null>
2         def456           1/2/2007      Blue      456       1/5/2007

Will become

UDF Table
ID    Recordtype    udf1               udf2     udf3
1      Workorder    Red                123     <null>
2      Workorder    Blue               456     1/5/2007
3      Building         Contracted    19        <null>

and

Workorder Table
ID       wonumber     date          
1         abc123          1/1/2007
2         def456          1/2/2007


There will be quite a bit of tables that will be split into more relational table. There will be some datatype conversions though most will likely remain the same. Some of these tables are very very wide currently.

The scripting part is what I'm fearing, though I'm not familiar with using auditing tables.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for all of your input. I looks like I'll be hammering out scripts. (SQL Prompt is gonna save me a ton of key strokes I think).

ptjcb: Why do you mention .net 2.0? Does that give you some further functionality with delploying an SSIS script? I'm not from a programming background, but I find using SSIS much more powerful and easy to manage.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.