Blog Post

Run a Job on SQL Azure (Using SSIS Package)

,

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-

  1. Drag Data Flow Task on the control flow window and double click on it to open the Data flow window
  2. Drag Excel source and configure it to the source file and the worksheet.
  3. Drag ADO.NET Destination.
  4. Create a new ADO.NET connection by selecting provider - .NetProviders\SQLClient Data Provider
  5. Put in the server name of a valid SQL Azure database.
  6. User SQL Server Authentication and type in the username and password.
  7. Go to ALL window and set the properties
  8. Select the target database.
  9. Configure ADO.NET destination with this new connection manager.
  10. Point to the target table.
  11. Map the columns correctly.
  12. Save the package by specifying the password (using encryptsensitivewithpassword)
  13. Go to ProjectProperties --> Debugging and set the Run64bitruntime property to False
  14. Run the package and make sure it runs without any error.
Creating a SQL Job
  1. Open SSMS and connect to the local server instance.
  2. Go to Sql Server Agent and make sure Agent service is running.
  3. Go to the Jobs Node and Expand it.
  4. Create a new job and set a step using Integration services.
  5. Select the file System option and point to the SSIS Package.
  6. Go to Execution options and select 32 bit runtime.
  7. Set the owner of the job as 'sa'.
  8. 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. 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating