SQL Server: Installing BIDS 2008R2 (Developer Edition)

After inserting the CD and running the Setup.exe the following User Account Control (UAC) dialog will appear;

SQL Server 2008 R2 UAC Dialog

Click "Yes".

The SQL Server Installation Center dialog will now appear;

SQL Server Installation Center

Click on "Installation" on the left (highlighted above);

SQL Server Installation Center (Installation Page)

Click "New installation or add features to an existing installation" (the top option on the right);

SQL Server 2008 R2 Setup

The installation program will run some tests to make sure your system can run the software. I'm installing onto Windows 7 Enterprise (32-bit). If there any any issues you'll need to resolve them before it will allow you to continue. Click "OK";

SQL Server 2008 R2: Product Key Dialog

After entering your product key click "Next";

SQL Server 2008 R2: License Terms

Click on the "I accept the license terms" checkbox (after reading it of course!) and then click "Next";

SQL Server 2008 R2: Setup Support Files

Click "Install" (if the installation of Setup Support Files fails with 1605 or 1608 error message then you may be interested in this post here - thank you Sage!);

SQL Server 2008 R2: Setup Support Rules

Click "Next";

SQL Server 2008 R2: Setup Role

Click "Next";

SQL Server 2008 R2: Feature Selection

Select "Business Intelligence Development Studio" (I've also chosen the Client/ Management Tools as they're often quite useful as well). Click "Next";

SQL Server 2008 R2: Installation Rules

Click "Next";

SQL Server 2008 R2: Disk Space Requirements

Click "Next";

SQL Server 2008 R2: Error Reporting

Click "Next";

SQL Server 2008 R2: Installation Configuration Rules

Click "Next";

SQL Server 2008 R2: Ready to Install

Click "Install";

SQL Server 2008 R2: Installation Progress

Wait for the installation to complete (takes about 10 minutes);

SQL Server 2008 R2:Complete

Click "Close".

You can now find SQL Server Business Intelligence Development Studio on your start menu;

SQL Server Business Intelligence Development Studio

NOTE: One of the fantastic anomalies with BIDS is that unless you have Integration Services installed locally you don't seem to actually do deploy to a remote server from within BIDS - you need to deploy to the file system and then use management studio to deploy to the server.

Also unless you install Integration Services *locally* you won't be able to test the deployed package on your system.

Read More

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.

Read More

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).

Read More