Import/Export without SSIS; BCP?

[smedvid]

I was just informed by the IT Gods that the new standard will be SQL Server 2008 and I need to convert existing SQL Server 2000 stuff. My concern is my DTS Packages; and that IT will not support or use SSIS; they opted for a ETL called Data Stage. Only a special group of developers in India can be used to develop/test Data Stage stuff. I am not comfortable having a dependency off shore...

Can I use BCP as an alternative to using a ETL COTS?

Basically I presently import data from large MS Access DBs into SQL Server tables. I also export tables (i.e. Lookup Data) from SQL Server into a single MS Access DB.

Can BCP read/write to MS Access DBs?

Thanks in Advance...
I wish that IT Supported SSIS! Life would be easier!


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[RiverGuy]

BCP is just for SQL Server and files. There are bulk copy objects in .Net which you could use. I'm not sure if you could get functionality for writing back to Access though. But it would definitely let you copy from Access to SQL.

I would push more for SSIS. SSIS is more than an ETL tool-it's also a database maintenance tool. I couldn't imagine migrating to a new version of software and instead of upgrading one part of the software (the data transformation part) simply removing it from the stack of allowed software. Another alternative would be to see if they will support an older SQL 2000 server for DTS only.

 

[PWise]

how about using linked tables in access

 

[gmmastros]

Or setting up a linked server from SQL to the Access DB(s).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

YOu can also get designtime tools for DTS installed on your SQl Server management studio to use in SQL Server 2008, we are using that until we can get all our hundreds of packages (all of which use things that don't directly convert) converted.

Alternatively, you can export access to text files and use t-sql code and bulk insert to import them. We can do an entire import using just t-sql code if we wanted to. However, your IT shop might frown on this if they notice you are doing it without permission since they are paying these overseas people to do this kind of work.

I would point out the risk in having imports and exports done using an unknown tool and done by people in another country. Are they really going to be able to fix prod immediately when a bad import or import causes users to not be able to log in? Will they even be at work in the same hours you are to be available to do an immediate fix?

It's crazy talk to not use SSIS. Even if you offshore it, you can hire people in India or elsewhere who know SSIS. YOu can hire support people here if need be who know SSIS. This other tool is a major risk for your business and you should write up a cost benefit anaylsis (including SSIS is free when you already have SQL Server) and show them in writing why this is a dumb thing to do. Make IT justify their decision when senior managers ask them about your analysis. Time to play hard ball with them.

"NOTHING is more important in a database than integrity." ESquared