Infosys | Microsoft

For one of our project we used SSIS package for migrating data but hit upon a few glitches in the course of our task. One needs to keep in mind the following points:

1. Since we will be running the SSIS generally on-premise to migrate data located on-premise to cloud we need to make sure the port number 1433 is opened for ctp.database.windows.net (or please check for the recent dns name assigned for Azure SQL). This port is needed for the TDS protocol for communicating with Azure SQL like any MS SQL server.
   
2. From outside the Azure cloud environment, only ADO.net type of connections are currently allowed, so one needs to make sure to select the correct connection type in the different SQL tasks/components to be used in the Data flow task in the SSIS package.
   
3. And last but very important; this issue we hit upon when we tried to migrate heavy data. For light data i.e. for few rows it went smoothly but for lot of rows like number in the range of 2000, we were getting error saying:

[ADO NET Destination [25]] Error: An exception has occurred during data insertion, the message returned from the provider is: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

And later after doing a lot of hit and try and taking help from our network administrator and Microsoft representatives, we found that it was mainly because our Infosys firewall was blocking the communication during heavy data flow. Connectivity was getting dropped due to smartdefence enabled on the firewall. It was set to monitor only mode and then everything just went fine.

After last month’s release, Azure SQL now supports bulk upload using BCP along with the addition of a new Firewall support feature. As per the Azure SQL Team; “the new firewall feature allows a customer to specify a list of IP addresses that can access their SQL Azure Server instance. Security is a concern for companies looking at storing data in the cloud and with this new feature one can rest assured that only the hosts one specifies will be allowed to connect”.

This holds for any communication made from outside Azure environment (including using SSIS package on premise) and the firewall will deny all connections by default, so before using one’s own Azure SQL instance, one needs to go to the SQL Azure Portal and configure the allowed list so that existing clients can continue to connect.