Remote connect to MS SQL

[Yegolev]

Anybody do any remote loading into MS SQL from a non-MS machine?  I have some ideas. just looking for prior experience and best-practices.

[UD_Delt]

You have like a million dollars handy? Informatica or IBM DataStage are your ideal large solutions unless you're talking slightly smaller scale.

Cheap(er) solution (well actually I have no idea what this costs but it's cheaper than a robust ETL tool):

Create an export of the data you need loaded into a flat file.

Use Connect:Direct (used to be called NDM) for the file transfer
http://www.sterlingcommerce.com/Products/AllProducts/Connect/direct.htm

Then use a DTS package to pull the data in from your local server.


Edit: This is assuming you're building a system that would need to do this more than once. If you're just doing it once then do an export into a flat file, FTP the file to your local box, and write a DTS package. Same basic steps but you can remove the software cost associated with a network data moving utility.

[Krakrok]

Bulk Insert for the win. With large amounts of data that is your best bet loading from a text file.

[Yegolev]

You have like a million dollars handy?

This is for work, so yes.  However I believe that's overkill.

Create an export of the data you need loaded into a flat file.

Use Connect:Direct (used to be called NDM) for the file transfer
http://www.sterlingcommerce.com/Products/AllProducts/Connect/direct.htm

Then use a DTS package to pull the data in from your local server.

After some conversation I may have convinced the guy to do something like this, although my solution was to use scp with keybased authentication, leaving the part about data insertion up to him.  I don't do Windows.  I believe he will be using Perl DBI, but as I said it's not my problem what happens on a Windows box.  On my machines, he gets to use Oracle, and I think that was what started this whole thing.

Edit: This is assuming you're building a system that would need to do this more than once.

Oh yeah, if it was a one-time thing then I think he should have been able to figure this out himself and would not want to try something wacky like remote SQL.

Krakrok, I will be keeping that link in my back pocket.  Thanks!

[UD_Delt]

You have like a million dollars handy?

This is for work, so yes.  However I believe that's overkill.

Depends on how often you are going to do things like this. Is data warehousing in the sights of your company? You could position yourself really well within your job if they are looking at building a data warehouse by being ahead of the game in understanding an ETL tool.

That's what I do right now. I'm an ETL Analyst/Architect which there is a huge demand for right now. To give you an idea, when I decided to leave my old company it took me all of 2 weeks to have 2 offers in hand. One in Cleveland, one in Atlanta. Plus a bunch of other interview requests.

Sure, it's IT flavor of the moment but for the next 3-4 years until people (in India) start learning the stuff you'd be valuable.

[Yegolev]

Oh, for data warehousing we are already heavily invested in SAP.  Our production SAP BW database is about 4.5TB in size.  This has little to do with that; it's actually an infrastructure inventory project that is tied into our new ITSM initiative.  I'm all about positioning myself, though, which is why I'm bothering to investigate this a bit even though it has very little to do with my job, and I might be able to spin this as saving The Company some money by using freeware or open source or whatever today's word is.

[UD_Delt]

I have little knowledge of SAP at the moment. The university I'm at now is heavily invested in PeopleSoft so I'm actively learning as much possible about the different components. It's a bit of a challenge since this is my first exposure at all to ERP tools.

Not to mention that the prior team that began the PeopleSoft EPM (data warehouse) implementation all left at the same time and didn't document a damn thing. So not a single person here knows anything about what they have or what it's doing.

[Yegolev]

So not a single person here knows anything about what they have or what it's doing.

This seems to be a common thread in the ERP technical field, SAP or JDE or otherwise.  The good part of that seems to be relatively low standards for entry.  Maybe that's just around here, though.