Load flat files from S3 into SQL Server 2008 R2

Hi, We have a few customers dropping files in Amazon S3. Could you please let me know how to load this data into SQL Server 2008 R2 database using SSIS? We are 2008 R2 BIDS environment.

Thanks in advance........

May 12th, 2014 5:35pm

There's a thid party connector available for this

http://www.cozyroc.com/ssis/amazon-s3-connection

also see

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_27733447.html

Free Windows Admin Tool Kit Click here and download it now
May 12th, 2014 6:09pm

Thanks Visakh for your response. Unfortunately I am not a member on experts exchange could you please let me know what solution they suggested.
May 12th, 2014 6:34pm

Hi ione721,

You can install the PostgreSQL ODBC driver so that you can use the .Net Providers\Odbc Data Provider for ADO.NET Source component to connect to the Amazon cloud storage. After that, you can use a OLE DB Destination to load the data to SQL Server database.

Reference:
http://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html 

Regards,

Free Windows Admin Tool Kit Click here and download it now
May 16th, 2014 8:26am

Thanks Mike.
May 20th, 2014 10:53pm

Hi,

You have few options... command line tools to download S3 files on local machine and then import to sql server using BCP.

Some popular command line tools

Or Use SSIS with some 3rd party Tasks like below... and the use DataFlow to read FlatFile into SQL Server.

SSIS Custom Task - Amazon S3 Cloud Storage Task Amazon S3 Cloud Storage Task
  • More than 30 cloud operations for Amazon S3 Cloud Storage (Such as Download, Upload, Copy, Delete)
  • Download small/very large Amazon S3 Cloud Storage file(s) to local machine
  • Upload small/very large local file(s) to Amazon S3 Cloud Storage
  • Parallel download/upload using multiple thread for faster operations
  • Support for Delete, Rename, List, Get Property, Copy, Move, Create, Set Permission and many more operations

Read more

Free Windows Admin Tool Kit Click here and download it now
May 18th, 2015 10:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics