Saturday, 11 March 2017

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. 




2 comments:

  1. I appreciate your work on Establishing Sql Azure using SSIS Package. It's such a wonderful read on SSIS. Keep sharing stuffs like this. I am also educating people on similar MSBI training so if you are interested to know more you can watch this MSBI tutorial:-
    https://www.youtube.com/watch?v=mrv0e6Qa4_M

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...