July 9, 2009 at 8:55 am
I'm currenly taking on the position of "default" DBA at my organization. We are trying to send data to another company and I'd like to know what all the options are. We need to send a small amount of data to this company on a daily basis SQL 2005 - SQL 2005.
1. Create SSRS report, schedule it to send a .CSV file to email. I think this would be the easiest approach from our end.
2. Create a SP and use webservice to push the data?
3. Push data to FTP site using SSIS?
4. Replication?
I'd like to know what the experts do. Any help would be much appreciated.
July 9, 2009 at 9:01 am
since the amount of data is very small, snapshot replication would suit you i guess as it would not pose much overhead in mainining it.
If you compare this with sending csv file, it'd not require much manual intervention.
I assumed 'another' company is on dedicated network.
If not, and you're sending the data to that company in csv files, i'd suggest using encryption; consider using PGP or GPG for this.
July 9, 2009 at 3:44 pm
Another option might be to send a database backup.
It really depends on the nature and purpose of the data. Will it be imported manually? Does it need to sync with existing data on the other side? Is there copy read-only or writable?
You also need to consider the skills of your opposite number - are they comfortable loading the info themselves, or will you need to write a job/package/routine of some nature that they can execute or automate?
July 9, 2009 at 5:11 pm
Another option that could be also be developed is a SQL Server Broker application to transfer the data.
July 9, 2009 at 5:34 pm
I kind of like the webservice idea with a sproc behind it.. It takes delivery out of your hands and makes it their responsibility to come get it..
CEWII
July 10, 2009 at 9:24 am
I would base a solution on the type of data (confidentiality) that's being delivered and how it is going to be consumed.
Sending database backups or reports when the company needs to import the data in a format internal to their systems would not help much. If you're sending public information then sFTP and encryption would be an overkill.
Maybe you should provide more information.
July 10, 2009 at 10:42 am
Since you wrote "another company" I would suggest a non database-specific format like CSV or XML. CSV is very compact, XML enables you to send structured data.
July 14, 2009 at 5:00 am
I think that the standard keeps on being flat files, either delimited or fixed width.
Seeing that you want to send data to another company, I take it that could be send to other companies as well in the near future. The second company might not have SQL Server, replication or might just use something totally different.
The only real option is to keep it KISS. Flat files.
Also try and stay away from comma delimited unless you use quotes as well to separate the data. Rather use a delimiter like ALT 145 æ to delimit your data. This has worked for huge files for me.
And last point, if its Windows environments, you can use WinZip to gain huge reduction in file sizes as well.
Just my 2c worth
July 15, 2009 at 6:07 pm
Hi,
Sending the data thru SSRS, webservice, backup, replication seems to be overkill here, best and the fastest way to send such small amounts of data would be to create a SSIS package that exports the data to a flat file and then FTP'ies it to the company's FTP location. You can then schedule this as a nightly Job.
If they want it secured, then you could do a SFTP operation too within the SSIS.
Thanks,
Amol
Amol Naik
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply