SQL 2005 Database - Designing Mass Load Process

Question

SQL 2005 Database - Designing Mass Load Process

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Alan. T. Hall of Fame Points: 3221 More actions · Answer 1

According to the SQL server rep here they are supporting 2 versions back.

For example, 2000 will be supported until the RTM of SqlServer version 10 comes out. I can't give you a date on that but 2008 has been released only a short time but 2010 is a decent guess. However, he also stated that what you have is what you get with DTS no further effort is going to be expeded on it.

Even to get the old dts package to run with 2008 you have to add the Microsoft SQL Server 2008 Feature Pack to get them to work.

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

You can check here:

http://support.microsoft.com/lifecycle/?p1=2852

It looks like the mainstream support has been "retired". There is still extended support available, but this is the option I was referring when I said you have to pay dearly for support.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

dwither_07 SSC-Addicted Points: 496 More actions · Answer 3

robertm (12/10/2008)
I think this is the misunderstanding.
You can use SQL Express edition as a data source within SSIS however you will need SQL Satndard edition to implement SSIS itself. That would be the ideal solution for you but the cost my be prohibitive.
The alternative would be to develop a suite of stored procs to manage this process something I have had to do many moons ago even before the advent of DTS!! This is more than doable and depending on your T-SQL skills can be very powerful and flexible.
The only outstanding issue will then be the scheduling. I'm not sure what is the best option here. If sqlcmd is compatible with Express then you could leverage the windows scheduler to run a cmd file.
All in all this would be a fairly bitsy solution but if that's all you have to work with then it might be you best bet.
Rob.

Hello Guys,

Sorry to have gone through so long about this thread since I need an approval from mgt. but I guess I failed to convinced them to buy license for the appropriate SQL edition to use SSIS.

So do I have any other alternative without SSIS?

Rob,

Do you know where I can find a references or site regarding what you've mentioned?

The alternative would be to develop a suite of stored procs to manage this process something I have had to do many moons ago even before the advent of DTS!! This is more than doable and depending on your T-SQL skills can be very powerful and flexible.

especially reading files, validating it though xml schema, check constraints and load it to maintenance database. I think having code to read files seems the least efficient but if I can show performance or technical advantages then that would be great.

Thank you for your great help.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 4

Do you have access / license for Visual Studio .NET?

dwither_07 SSC-Addicted Points: 496 More actions · Answer 5

Alan (1/13/2009)
Do you have access / license for Visual Studio .NET?

Yes I do have access/license for Visual Studio .NET 2005 and 2008.

robertm Ten Centuries Points: 1096 More actions · Answer 6

Hey,

If you have the ability to develop something in .NET then that's by far the best path to follow.

You should be able to build a windows service that will monitor your folder(s) and load the data after it's been checked.

As a start here's a nice article explaining how to build a windows service (very straight forward in .net) that monitors a folder.

http://www.blackwasp.co.uk/WindowsService.aspx

After you've looked at this, please reply letting everyone know if you think this solution will work for you and then perhaps people will be able to give you some more advice on how to achieve any aspects of your solution that you're still not clear about.

Hope this helps,

Rob.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 7

Great with access to visual studio you can create the xml and xslt schema and validators. After they are validated you can then use ADO.NET to insert the records into the database. VB.NET 2008 has extremely strong XML tools and you should be able to develop the application quickly. This can either be put into a console application and run using windows scheduler or created as a windows service. Both would meet you needs.

Sorry about the confusion earlier.