Good Afternoon,

I am trying to work out the best way to export a azure database everyday to a local instance of SQL Server. I understand that it is possible to do this manually using the SQL Server 2012 tools and manually import the BACPAC file. However as this will be a daily task I was wondering if it would be possible to automate this process, ideally by using SSIS if possible.

Any help would be great.

Thank you.

Steve

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator 16 hours 6 minutes ago

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator 15 hours 56 minutes ago

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator 15 hours 51 minutes ago

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Monday, August 03, 2015 3:18 PM

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Monday, August 03, 2015 3:18 PM

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Monday, August 03, 2015 3:18 PM

I think the answer to your problem might be PowerShell embedded in SSIS. 

1.  run a sql export to azure blob storage in powerhsell Start-AzureSqlDatabaseExport [-SqlConnectionContext] <ServerDataServiceSqlAuth> [-StorageContext] <AzureStorageContext> [-StorageContainerName] <String> [-DatabaseName] <String> [-BlobName] <String> [ <CommonParameters>].
2. copy that down to local storage $myblob = Start-CopyAzureStorageBlob -srcUri $MysrcUri1 `
                                    -DestContainer $MycontainerName `
                                    -DestBlob $MyDBFile `
                                    -DestContext $MyContext
3. Import with sqlcmd.

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Thursday, July 30, 2015 3:12 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Monday, August 03, 2015 3:18 PM