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 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
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
Now in the SSIS package in ADO.NET destination make the configuration as below
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.