This blog post discusses two methods in SSIS which can be used for connecting to Azure instances.
The attempt is to copy an on premise table along with the data to an Azure instance.
We can use ADO.NET connection manager to connect to Azure instance. The sql client as well as odbc client providers may be used for setting up the connection. Lets see both the methods here
1. Using SqlClient provider
The package consists of a Data Flow Task with structure as follows.
The OLEDB Source is straightforward which just connects to an on premise database and selects data from a table. The ADO .NET destination looks like below
The connection manager would be configured as follows
The server name,database and username can be obtained by logging into Azure management portal.
and clicking on view connection strings link which will show the info as below
While trying to run the package you may get a error like below
This is because you have created the table from within SSIS and any table should have clustered index while created in Azure. For avoiding this you’ve to connect to Azure database using Azure management portal or SQL Management Studio and create a clustered index to table using CREATE INDEX statement like below
CREATE CLUSTERED INDEX IDX_Clust ON [ADO NET Destination] (OrderItemID)
2. Using ODBC Provider
The ODBC Client configuration is a little more trickier than using SqlClient. This requires creating a new DSN from the machine and then using it in connection manager.
The DSN configuration would be as follows
Fill in username and password in the next window
Give the DB details in the next window. This is required especially when you want to create new objects in the db otherwise the default connection would be to master database which will fail due to lack of permission while creating new objects.
Select the default properties in the next windows and test the connection in the last window.
Once this is done the DSN would get created successfully.
Now in the SSIS package in ADO.NET destination make the configuration as below
Select the DSN previously created in the system. Then give the username and password as given in the Azure connectionstring. Test connection to make sure it completes successfully.
Once this is done rest of steps are same as in 1st method and you can see the package executing successfully.
Go back to Azure management portal or to SQL Management Studio and you can see the object created in Azure database with all the data transferred from the on premise instance.
