The goal of this blog post is to show that it is as easy with SQL Azure as with SQL Server to push data with SSIS.Le but de ce billet est de montrer qu’il est aussi simple de pousser des donn%u00e9es avec SSIS vers SQL Azure que vers SQL Server.
In this sample, let’s use SQL Server 2008 R2, which is the latest SQL Server version in production (1).Dans cet exemple, utilisons SQL Server 2008 R2, qui est la derni%u00e8re version sortie de SQL Server (1).
The scenario is to push some data from SQL Server 2008 R2 on premises to SQL Azure in the cloud. This is done with SQL Server 2008 R2 Integration Services (SSIS).Le sc%u00e9nario est de pousser des donn%u00e9es venant de SQL Server 2008 R2, %u00e0 demeure, vers SQL Azure, en nuage, et ce avec SQL Server 2008 R2 Integration Services (SSIS).
If and when starting from an empty SQL Azure database, in order to create the schema, one would typically use SQL Azure Migration Wizard which has an option to generate schema only.Dans le cas o%u00f9 l’on part d’une base de donn%u00e9es SQL Azure vide, de fa%u00e7on %u00e0 cr%u00e9er le sch%u00e9ma, on utiliserait typiquement le SQL Azure Migration Wizard qui a une option pour ne g%u00e9n%u00e9rer que le sch%u00e9ma.
Here are the steps to create a very simple SSIS package and execute it (in debug mode):Voici les %u00e9tapes pour cr%u00e9er un package SSIS tr%u00e8s simple et l’ex%u00e9cuter (en mode debug):

image

image

image

image

image

image

image

image

image

image

image

image

 

image

image

image

image

image

image

NB: the SSIS package needs an outbound access to port 1433 in order to connect to SQL Azure. The SQL Azure firewall must also have been opened for the public IP address the SSIS package will use to access the Internet.NB: Le package SSIS a besoin d’un acc%u00e8s au port 1433 en sortie vers Internet pour se connecter %u00e0 SQL Azure. Le pare-feu SQL Azure doit %u00eatre ouvert pour l’adresse IP publique avec laquelle le package SSIS sort sur Internet.

image

image

image

image

image

image

 

Here is a synopsis of what we just did. Data was copied from SQL Server 2008 R2 to SQL Azure thru SSIS exactly as it would have been from SQL Server to SQL Server.Voici un r%u00e9capitulatif de ce que nous venons de faire. Les donn%u00e9es ont %u00e9t%u00e9 copi%u00e9es depuis SQL Server 2008 R2 vers SQL Azure %u00e0 travers SSIS exactement comme cela aurait %u00e9t%u00e9 fait de SQL Server %u00e0 SQL Server.

image

This sample SSIS package was very simple, because its goal was to show connectivity is seamless. Of course, in real life this package would contain much more shapes in order to copy several tables, transform data and so on.Cet exemple de package SSIS est simpliste parce que le but ici est de montrer que la connectivit%u00e9 est ais%u00e9e. Bien s%u00fbr, dans la vraie vie ce package contiendrait bien d’autres %u00e9l%u00e9ments de fa%u00e7on %u00e0 copier plusieurs tables, transformer les donn%u00e9es etc.

Smile

Benjamin


(1) SQL Server 2012 is in Release Candidate 0 for now so I expect companies to have SQL Server 2008 R2 deployed(1) SQL Server 2012 est en Release Candidate 0 actuellement et je m’attends donc %u00e0 ce que les entreprises aient d%u00e9ploy%u00e9 SQL Server 2008 R2.


Blog Post by: Benjamin GUINEBERTIERE