My First SSIS + Azure SQL Task

I need to do a task transferring data from SQL Server to Azure cloud. And this is really a good opportunity to refresh my SQL skill. One of my co-workers Haitao, who is really expert in SQL, demonstrates the SSIS. I love the way it processes data.

The core of the task is to transfer the data from SQL to SQL Azure. So the first task is to find the data flow task. I am a visual person, the colorful icon make it stands up and very easy to find out. Two yellow dots with a green arrow. :-)

Once you double click the Data Flow task on the design surface. You have to provide source and target. I had trouble using ADO.net when I set up the configuration file, so I use the OLE DB tasks whenever it is possible.

I need to create several tasks. Another lesson I learnt is the arrow between tasks are "precedence". If a node's proceeding tasks are all finished, this tasks can start right way. So you might see some tasks are executed simultaneously. 

Variable is what I I use exchange the statements from one task to the other task(s). I use select count(*) to check the existence of a table and result is put into a variable.

Be very careful, if it is ADO.net, the result uses index. For example, your SQL statement is select count(*) as AA from TableAA. OLE DB allow you use AA in the result set, but ADO.net only accept 0. This costs me 1 hour.. :(

Because the development environment is different from production environment, I have to make a configuration file. The configuration file can be created by right click anywhere on the design surface (not on any tasks). The right-click menu has the "Package configurations..." item. The connection string is what I wanted to put in the configuration file. The wizard does not put the password in the configuration file, so every time I made changes to the configuration file I had to manually add the password in the connection string.

After I successfully put the connection string into the configuration file, I tried to push the limit by dynamically generate SQL statement from the variable stored in the configuration file. The following screen shot should solve the problem pretty easy. 

Overall, I am very happy about the SSIS and its tooling support. Well, my stomach is really empty. I will blog next time.