Share via

Using SSIS with SQL Azure databases

  • Article

I’m quite surprised how easy it is to setup an SSIS package to read and write data to a SQL Azure database.  A simple data flow task with an ADO.NET Destination is easy to setup, just make sure the SQL Azure table has a clustered index otherwise writing data will not work (as this is a pre-requisite). 

image

The SSIS team have added a BULK INSERT option on the ADO.NET Destination in SQL Server 2008 R2 to improve performance, this blog entry covers the details: https://blogs.msdn.com/mattm/archive/2009/11/12/something-new-for-ssis-in-sql-server-2008-r2-november-ctp.aspx

On the topic of indexes, it looks like there is a bug in the scripting engine in SQL Server 2008 R2.  Scripting the index as DROP to an SSMS window returns the following however the ONLINE syntax is not supported with SQL Azure

image

Comments

  • Anonymous
    December 08, 2010
    Can you run the package in azure, or do you only read data, and run the package localy?
  • Anonymous
    February 02, 2011
    Assuming that this package runs locally, can you push data and pull data from SQL Azure?
  • Anonymous
    February 02, 2011
    Hi - the package will only run locally as SSIS is not currently supported in Azure.   It is possible to read from SQL Azure databases using SSIS (ADO NET Source)
  • Anonymous
    November 13, 2011
    Hi,i got a lot of knowledge about SSIS and SQL azure databases.... thanks for sharing this post..http://www.error628.com