Move it all into your new home. Sql 2005.

Data conversion.
Get your data into SQL Server 2005, where it belongs.

Q: How do I get the data I have extracted from [some system] into SQL Server 2005?

TSql OpenRowset.

If your source system talks OLEDB (they all do), I would prefer OPENROWSET.
-- Snippet for Access
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\dir\mymdb.mdb';'admin';'',objectname)
GO
-- EoSnippet for access


Integration Services

If you need to clean your data, or take data from many types of systems and combine them, you can use Integration Services. This takes some work to be done properly (it looks like programming like DTP is here, but it is not quite there jet). Here you can script (or low-level create, for that matter) your own components using the AI stuff you use in your day to day programs (kidding)


Import program

If you are happy with neither OPENROWSET nor SSIS for some reason, often I’m not, you have still a few ways other ways of doing it.
- Writing a CLR Stored Procedure is the COOL way to do it. Showing off your tech skills.
- Using bcp might be the DBA way of doing it.
- Use the OLEDB provider in Delphi, ODBC with C/C++ or even JDBC with Java will work. (I’ve tried the two OLEDB, anyway)
But the best way is to create a Forms app and ADO.NET (in my not so humble opinion). You can do the max of it by reading and writing data at the same time (storing the intermediate in memory), but that would be like re-eventing the SSIS(DTS) wheel. No, just relax and insert your data with ADO.NET (actually, I am not an expert on ADO.NET, because I need to know all the other stuff too. It’s an obsession)


Happy processing and create SSIS packages for the lot. Even if you don’t need to hit the “Data Flow”-button or use the log capabilities.