SSIS: Automated Job Scheduling of SSIS Packages with SQL Server Agent

In my last post I described how to deploy and execute a SSIS package from SQL Server Management Studio. In this post I will reuse the same package, but will execute it automated by creating a Job schedule via SQL Server Agent.
This is useful for tasks that need to be run daily or recurringly. A typical example is the daily load of a Data Warehouse. These loads run usually after midnight on a daily basis. So running it manally every night is neither handy nor healthy :)

First step is to create a new Job in SSMS. Go to the SQL Server Agent folder, right click and select New Folder

In the General tab you define the name of the Job and then browse to the Steps tab.
Click on new and fill in the fields as described below:

Now we need to define a time and how often the Job should be executed.
There is a drop down for selection in the Schedule Type region. I will go for Recurring since I want to schedule it on a daily basis. This will be defined in the Frequency region under the Occurs drop down. You can select between Daily/Weekly/Monthly.

After the package was executed, you can check if it was succesful or not by clicking on View Histoy.

You need to ensure that the SQL Server Agent account which is set, has the rights to schedule the package. Otherwise you may end with a not succesful schedule.

SSIS: Create SSIS Catalog and deploy SSIS Package

This post will focus on how to deploy your dtsx package to SSMS and how you can execute the project from there.

First of all you need to ensure you have already created the SSISDB catalog.

Go to the object explorer and see if you can find a folder under Integration Services Catalogs. If not, simply right click on the mentioned folder and click on Create Catalog.

After having created the SSISDB catalog right click on it and select Create Folder and give it an appropriate name. Open the toggle and you should find 2 more folders (Projects & Enviornments) assigned to it.

Now you can either right click on Projects and import the dtsx file from SSMS or deploy the project from Visual Studio (SQL Server Data Tools). I will go for the latter.

OK, so go to your SSIS project in SSDT and right click in the solution explorer on the top folder and click Deploy.

Select server and for the path, the SSISDB folder we just created.

Click through and once you reached the Review part simply click the Deploy button.

Now should you see your dtsx package in SSMS under the Projects folder.

The package is now ready to be executed on demand. All you need to do is right click on the dtsx file and click Execute

Please check the video above to get a more precise picture.  

SSIS: MERGE JOIN FOR INSERT AND UPDATE (UPSERT)

Today I would like to present a SSIS package that will handle the insert and update task within the target table.

Let's start from the beginning...
First of all we can go right to the Data Flow tab.

We will need 2 OLE DB Source items from the SSIS Toolbox. One will be used to capture the source table, one for the destination table. In a second step, we will need to sort the primary key before we join the 2 tables.
The first steps can actually be replaced by an OLE DB Command item. Actually later is even recommeded since the Sort item is quite expensive. Since I have only a very small dataset and due to demonstration reasons, I will go for this option now.
Next item we need is Merge Join. Since I know the full set is captured in source and the destination table is missing the delta load, I will select Left Outer Join here. If it's vice versa, you will need to go for an Full Outer Join.
In the next step, we need a Conditional Split item from the toolbox. Here we will distinguish between records that are missing in the target table, and records that have been updated in source, but not yet in the target table.

The new records can be simply loaded into the destination table by using the OLE DB Destination item.
For the updated rows, we will need a Stored Procedure that will map the new rows and update them in target.

In a final step we will use an OLE DB Command to call the procedure and handover the parameters.

Now we will have the full set in both tables. Please check the video above to get a more precise picture.