Requirements: A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or above
Scenario: Dump Excel File Contents to a Table in SQL Azure
Steps:
Develop SSIS Package-
- Drag Data Flow Task on the control flow window and double click on it to open the Data flow window
- Drag Excel source and configure it to the source file and the worksheet.
- Drag ADO.NET Destination.
- Create a new ADO.NET connection by selecting provider - .NetProviders\SQLClient Data Provider
- Put in the server name of a valid SQL Azure database.
- User SQL Server Authentication and type in the username and password.
- Go to ALL window and set the properties
- Select the target database.
- Configure ADO.NET destination with this new connection manager.
- Point to the target table.
- Map the columns correctly.
- Save the package by specifying the password (using encryptsensitivewithpassword)
- Go to ProjectProperties --> Debugging and set the Run64bitruntime property to False
- Run the package and make sure it runs without any error.
Creating a SQL Job
- Open SSMS and connect to the local server instance.
- Go to Sql Server Agent and make sure Agent service is running.
- Go to the Jobs Node and Expand it.
- Create a new job and set a step using Integration services.
- Select the file System option and point to the SSIS Package.
- Go to Execution options and select 32 bit runtime.
- Set the owner of the job as 'sa'.
- Run the job and it would load the data from excel file stored in your local to the table residing in Azure database.
Let me know your suggestions/observations.
See ya.
