Automating Your ETL - Creating a SQL Agent Job

The amount of data companies receive is growing larger all the time. Oftentimes, manual executing is just not efficient. Working with a global workforce means large amounts of data at all hours of the day from different sources.

We’re going to look at automation today, through SQL Agent Jobs inside of SQL Server Management Studio. Utilizing SQL Agent Jobs will allow you to execute tasks while you’re not even in the office.

In this demo, I’ll walk through a simple introduction to SQL Agent Jobs. I’ll be kicking off a Task Factory package that includes our REST Source component

• I start inside of Visual Studio and you’ll see a pair of Task Factory components, REST Source and Excel Destination.
• For this use case, let’s say I’m a small-time business owner, putting up items for sale on eBay. To keep up with what’s going on in the market, allowing my small business to grow, I frequently go through and check on prices.
• I could execute this from Visual Studio, but I want to continually get this info and set certain times of day to schedule these tasks, without having to be at my computer.
• Next, I go into SQL Server Management Studios where I created a job called eBay Scrape
• A best practice is to set up by providing job name, owner, job category and description.
• Then go into Steps where you can schedule and automate all your development!
• My one step is called Execute Package, the type is a SQL Server Integration Services (SSIS) package. I run it and grab the specific package.
• Next, I simply execute the item I chose. I also went ahead and attached a Notification Framework as well (using a component in our BI xPress tool). This allows me to know the moment a package finishes executing, and I can set criteria to be notified on warnings, errors and successful executions, sent right to my email.

Simple and time saving, right? For more information on using SQL Agent jobs to schedule tasks during off hours, you can visit the Microsoft website where they offer great walkthroughs of the entire SQL stack. If you want to try out using SQL Agent Jobs or any of the other 65+ components in Task Factory, visit our website to download a free trial today.