SSIS: Creating a Job To Run An SSIS Package On A SQL Server

For this blog post I'm going to use the test SSIS package I created as part of the "Exporting Query Results To A Flat File"-blog post, and had previously deployed to SQL Server (see "Deploying A Package To A Remote SQL Server") because it's all setup and I know it works.

Start SQL Server Management Studio;

Management Studio: Connect to Server

Make sure you select "Database Engine" in the "Server type" drop down. Click "Connect";

SQL Server: Object Explorer

Expand the "SQL Server Agent" node in the "Object Explorer" (left-hand side) and then right-click the "Jobs" node;

SQL Server: Creating A New Job

Click on "New Job ...";

SQL Server: New Job Dialog

Enter a Name (I've chosen "SSISDataExportDemo") and then select "Steps" from the list on the left;

SQL Server: New Job Dialog (Steps)

Click on "New..." (highlighted above);

SQL Server: New Job Step Dialog

Enter a "Step Name" (we're only going to have one so it's not really important) and then change the Type from "Transaction SQL script (T-SQL)" to "SQL Server Integration Services Package" and a lot of new options will appear;

SQL Server: New Job Step (SQL Server Integration Services Package)

You need to select the SQL Server you wish to run the package on (doesn't have to be the one you're currently connected to) and then select the name of the package. Once you've done that click "OK";

SQL Server: New Job (Steps)

As you can see the new step we've just created is now listed.

It is now ready to run but if the job is only going to be run as a one-off you might want to click "Notification" on the left and select "Automatically Delete Job" (when the Job Succeeds);

SQL Server: New Job (Notifications)

This way you'll keep your SQL Server tidy but you should bear in mind that if you do this the job will run immediately and then be gone so you won't be able to see anything in the following steps!

Click "OK" when you're done (a few seconds will pass while the script to create the job is executed on the SQL Server).

Now you can expand the "Jobs" node on the SQL Server and find the new Job you've just created (usually it's inserted at the bottom of the list) and right-click it;

SQL Server: Job Options

Choose "Start Job at Step...";

SQL Server: Start Jobs

The job will run and will take as long as it takes and then you'll see the result (as above). Now that we know the Job is there and works we can schedule it by right-clicking it and bringing up "Properties" and select the "Schedules" page;

SQL Server: Job Properties (Schedules)

Click on "New ..." to create a New Schedule;

SQL Server: New Job Schedule

I've changed the "Schedule type" to "One time" and set it to run in a few minutes but there is a lot of flexibility to schedule the task as and when you see fit.

When you're done click "OK", and then "OK" again to save your changes.

SSIS: Deploying A Package To A Remote SQL Server (And Testing It)

The blog post covers deploying a package onto a server (and then running it). I'm going to use the test package I created in the blog post "Exporting Query Results To A Flat File" as it's as good as any and doesn't rely on anything tricky. I also know that the domain user who is running the Server has permissions to see the G drive I'm trying to write to so I can eliminate that as a potential error!

Open the project in BIDS and right-click the project in "Solution Explorer" (on the right);

SSIS: Solution Properties

Select "Properties" at the bottom;

SSIS: Solution Property Pages

Select "Deploy (BIDS Helper)" in the panel on the right and then change the "DeploymentType" to "SqlServerDestination";

SSIS: Changed Deployment Settings

While it defaults to "localhost" I always tend to pick a different machine to deploy to if possible or specify the name of the machine in full - I just worry that "localhost" could change at any time (you could open and run it on a different machine) whereas if I specify a machine it will remain constant.

When you've entered the server you with to use click "OK".

Right-click the project in the "Solution Explorer" view again;

SSIS: Deploying A Project

And this time select "Deploy";

SSIS: Output Showing A Successful Deploy

Now the package has been successfully deployed to the SSIS Server.

To check it's successfully worked open up SQL Server Management Studio;

SQL Server Management Studio: Connect to Server

Make sure you select "Integration Services" in the "Server type" drop down and click "Connect".

Go to the "Object Explorer" (on the left) and expand the nodes under "Stored Packages" until you find the package you've just deployed;

Management Studio: Deployed Package

Now right-click the deployed package;

Management Studio: Package Options

Select "Run Package";

Management Studio: Execute Package Utility

Click "Execute";

Management Studio: Package Execution Progress

As you'll see I've expanded the window so you can see the complete set of messages for executing this package. This will appear over time as the package executes so could take anything from a few seconds to a few hours to shows you everything (depending on the complexity and volume of the SQL you're running) but it will complete (either with an error or success).

Click "Close".

SSIS: Exporting Query Results To A Flat File

This is a fairly quick blog post to just go through the process of taking some SQL, running it against a database, and exporting the result into a Flat File. We recently followed pretty much this process to allow us to schedule a non-interactive job to export our entire Financial General Ledger into a flat file to give to our auditors.

The first step is to start BIDS, go to the File menu and select "New Project";

BIDS: New Project Dialog

Select "Integration Services Project", give it a name and select a location to save it to then click "OK". you will then be presented with the new project;

BIDS: Blank SSIS Project

First things first. In the "Solution Explorer" (right-hand side) you'll notice that there is a file called "Package.dtsx". If you deploy this to a server then this is exactly what it will be called - rename it to something else. I usually use the name of the project (without spaces). A dialog will appear;

SSIS: Package Renaming Dialog

Click "Yes", then you'll see something like this;

BIDS: Solution Explorer

Now let's start building the process. Drag a "Data Flow Task" from the Toolbox (on the left) into the designer;

BIDS: Adding a Data Flow Task

Once you've added the new "Data Flow Task" double-click the icon to open it;

Now assuming you're going to be dragging the data down from an OLE DB Source (like me) you need to drag one of those into the designer;

BIDS: OLE DB Source

The little red "x" at the right-side of the new icon means you haven't configured it yet, double click the icon to open the configuration screen;

BIDS: OLD DB Source Editor

The first thing you need to do is connect to your database. This will involve clicking "New" to invoke the "Configure OLE DB Connection Manager" dialog;

BIDS: Configure OLE DB Connection Manager Dialog

In here you can setup connections for SSIS. Once you've created a connection it will be available here for you to use in other SSIS projects.

When you're happy with your connection (i.e. it works!) make sure it's selected and click "OK".

You are now returned to the previous screen with your selected connection highlighted;

BIDS: OLE DB Source Editor with Selected Connection

You have two choices you can either directly pick a table or enter some SQL. I'm going to enter some SQL so you change the drop down from "Table or view" to SQL Command;

The SQL I'm using is;

SELECT *
FROM OPENQUERY ( XXX, 'select * from lot_genealogy_whereused')

This retrieves data (via SQL Server) from our Oracle e-Business Suite. Paste the SQL into the "SQL Command Text" entry box;

BIDS: OLE DB Source Editor with SQL Command

Click on "Parse Query" (to make sure everything is working OK) and then click "OK" at the bottom.

This closes the dialog and returns you to the main window;

BIDS: OLD DB Source (No Errors)

You'll notice that the little red "x" has gone. Now drag a "Flat File Destination" (NOTE it's DESTINATION, not Source) into the designer and connect them up (drag the green arrow from the OLE DB Source to the new object);

BIDS: OLE DB Source with Flat File Destination

You'll notice that the red "x" is back on the Flat File Destination object. Double-click it to configure it;

BIDS: Flat File Destination Editor

To create a new "Flat File Connection Manager" click on "New";

BIDS: Flat File Format

I'm going to use "Delimited" (basically CSV). Click "OK";

BIDS: Flat File Connection Manager Editor

Click on "Browse" to select the destination where you want to place the file. It's important that the destination you select will be visible to the server you're going to deploy it to (using UNC paths if it's on a different server);

BIDS: Flat File Connection Manager Editor

The other thing I change is to add in a double-quote as a Text Qualifier. the main reason for that is if I have data (like mobile numbers) in the source then I don't want Excel treating it as a number when I open it - including a text delimiter solves this problem.

Remember to check the "Column names in the first data row" checkbox if you want them!

Click "OK" when you're done.

BIDS: Flat File Destination Editor

Click on "Mappings" (on the left) if you fancy a look at how the columns in your source are being mapped to the destination;

BIDS: Flat File Destination Editor (Showing Mappings)

Click "OK" to return to BIDS;

BIDS: Designer Window

The designer now shows you that the little red "x" has gone and you're ready to test your package.

Click "Run"/"Start Debugging" and after a few seconds the task will run and you'll start seeing records being written to your file. When the task is complete everything will go green;

SSIS: Task Successful Completion

I'll create a separate blog post for how to deploy the SSIS package to a server so it can be run as a background task (200k rows took around 10 seconds, but if you had 20m you wouldn't want to sit there watching it).

SQL Server: Installing Microsoft SQL Server 2012 On a Development Windows 2008r2 (SP1) System

This is a simple step-by-step guide to installing Microsoft's newest version of SQL Server with Reporting Services in stand-alone mode (the alternative is SharePoint Integrated, I'll deal with all the steps necessary to do that installation separately in a few days/weeks time).

I'm using the developer edition as downloaded from Microsoft MSDN, the ISO name is;

en_sql_server_2012_developer_edition_x86_x64_dvd_813280.iso

I've highlighted the build version. If past experience is anything to go by later releases will follow pretty-much the same installation pattern.

The first step is to install your Windows 2008R2 box and get it fully upgraded to the latest patch levels (SP1). Then you can run the setup.exe on the installation DVD;

SQL Server 2012: Installation Centre

Select "Installation" from the list of options on the left;

SQL Server 2012: Installation Options

Select "New SQL Server stand-alone installation ..." which is the top option on the right. After a few seconds wait (but I guess that probably depends on the speed and power of your system!) the following dialog appears;

SQL Server 2012: Setup Support Rules

The installation program has checked your system and, on the system I'm using, found nothing that prevents the installation from proceeding. If you are interested in seeing the "Detailed Report" I've updated it to Google Docs here (with a slight bit of editing to remove machine names).

Once you've got a good set of passes click "OK". A "please wait" dialog will appear and after a few seconds the next dialog;

SQL Server 2012: Product Key

You now need to enter your Product Key. If you are using the MSDN edition then it will be pre-populated, if you don't have one when you can just select one of the "Free" editions. Once you've entered the details click "Next";

SQL Server 2012: License Terms

No installation process would be complete without a 20-page Licensing Agreement and this one is no different. After you've read the entire document (you do do that right? *cough*) click on the "I accept the license terms". I also select the "Send feature usage data to Microsoft" and if you don't you'll only have yourself to blame when the features I use and tell them about are prioritised for improvement and the features you use but keep secret about aren't ... ;-)

Click "Next";

SQL Server: Install Setup Files

This dialog will only appear for a few seconds, as soon as the installation files have been successfully installed you are presented with the next dialog;

SQL Server: Setup Support Rules

Again I've made the Detailed Report for this check available via Google Docs here. As you'll see there is one Warning related to the "Windows Firewall", I'm going to ignore this and just move on.

Click "Next";

SQL Server: Setup Role

The default option, "SQL Server Feature Installation" is the one I'm after so I'll just click "Next";

SQL Server: Feature Selection

And this is the part where things start to get complicated. What exactly do you want your SQL Server to be doing? I'm going to install pretty much everything except the SharePoint integrated features (the first two shared features; Reporting Services - SharePoint, and Reporting Services Add-in for SharePoint Products). Clearly I'm building a development box so it's usually better to have too much rather than too little so I can use it for many different purposes.

Clearly if you're building a test (or production) machine you need to be a lot more careful about what you select on this page. As you select features configuration pages appear on the left to allow you to configure them during the installation.

Once you're done click "Next";

SQL Server: Installation Rules

So we now have another check, the third, to make sure the system is capable of running the options I've selected. And now it "Failed" as "Microsoft .NET Framework 3.5 Service Pack 1 is required". The instructions for enabling this feature I have also blogged about, click here. When I did this no-reboot was required.

After you've enabled the feature click on "Re-run" in the dialog;

SQL Server: Installation Rules (Attempt 2)

As you can see the check that had previously failed has now passed. The Detailed log is available (via Google Docs) here.

Click "Next";

SQL Server: Instance Configuration

There isn't anything here I need to change so just click "Next". There will be a brief pause while the installation program checks to see if sufficient disk space exists to install the options you have selected. After a few seconds a report will be displayed;

SQL Server: Disk Space Requirements

Click "Next";

SQL Server: Server Configuration

It's pretty unlikely that you'll want to change anything here (all these are services you can always change later). The one thing you might want to check if you are deploying to non-English customers is that the correct options are selected under the "Collation" tab - this is especially true if your system will be used to store multi-byte data such as Chinese, Japanese, Korean, etc. characters.

Click "Next" when you're done;

SQL Server: Database Engine Configuration

I always use "Windows authentication" and (as it's a development machine which will spend 90% of it's time off!) I usually add in a few AD groups representing the entire company (we are an IT Service company) on the basis that I never know who I might want to share it with. This is your chance to secure the system either as tightly or loosely as you wish.

Click "Next";

SQL Server: Analysis Services Configuration

And the same again really. You need to enter the users who will have access to the Analysis Services. Once you're happy with this click "Next";

SQL Server: Reporting Services Configuration

The default option, "Install and Configure", is the option I'm interested in so just click "Next";

SQL Server: Distributed Replay Controller

Whilst appearing to be exactly the same as other "Pick the users" dialog box this one is subtly (invisibly!) different; you cannot select groups. If you try when you click "Next" you get an error;

The specified account 'XX\YYYY' for setting 'CTLRUSERS' is a group account. You can only use a user account.

Add in the users for this feature and click "Next";

SQL Server: Distributed Replay Chat

Enter the name of your controller (or leave blank) and then click "Next";

SQL Server: Error Reporting

I always check this check box (to send error reports to Microsoft) so that errors I encountered will hopefully be dealt with in future releases! Click "Next" when you're done and another round of checks will be executed and after a few seconds you will be presented with a report;

SQL Server: Installation Configuration Rules

Again I have uploaded the detailed report to Google Docs here. Click "Next";

SQL Server: Ready To Install

Click "Install" to begin the install process. The installation itself on the development system I was using took around 25/30 minutes. After the install is complete you will see the following dialog;

SQL Server: Computer Restart Required

Click "OK" and under this is the final installation report;

SQL Server: Complete (Install Report)

I've attached the (edited) log file generated by the installation here.

A restart is required, after the restart running Internet Explorer and pointing to the the SQL Server Reporting Services URL will (after logging in) take you to the standard webpage.