Friday, 26 June 2009

SQL Server Job Error: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

If you deploy some DTSX packages to SQL Server, and create an Agent job to run them, you might see this error:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

The workaround is probably simple. When deploying to your SQL Server and you're on the Specify Target SQL Server page of the wizard, select the 'Rely on server storage for encryption':

That should probably do it. If not, check out this thread on MSDN.

Thursday, 25 June 2009

SQL Server Integration Services: Setting Input Filenames Dynamically from a SQL Server Table

Being a recent convert from SQL Server 2000, I was used to the DTS framework for importing files to the database. From recent blog posts, you will see that it's been a learning curve in getting completely up to speed with SQL Server 2005's Integration Services.

When loading files into a database, I obviously found it important to dynamically set the file path so that values were not hard coded, otherwise it became a maintenance nightmare. Especially when promoting the packages to different environments.

I decided that for my application, the best way to dynamically set these properties was by creating a database table with the details on each file which was to be imported. Then I would extract the filename into the DTSX at runtime and use that to import the file. This way, the filenames would be picked up according to whatever environment the DTSX is running in, and there would be no hard-coded filenames to deal with.

So I thought I'd share a quick description of how I went about it, as much as a reminder for myself in future as for others who might stumble across this blog.

The table which contains the import file details is set up as follows:

CREATE TABLE [Import_File_Specifications](
[ImportFile] [nvarchar] (100) NOT NULL ,
[FileName] [nvarchar] (255) NULL ,
[FileExtension] [nvarchar] (30) NULL ,
[Location] [nvarchar] (255) NULL ,
CONSTRAINT [PK_Import_File_Specifications] PRIMARY KEY CLUSTERED ( [ImportFile] )

So I loaded the file specifications into there, and at runtime the DTSX would run some SQL against the table to build the filename based on the folder location, filename and file extension.

All I did was (this is assuming you're importing a flat file - tab delimited, csv etc):

1. Create a Package Variable to contain the filename of type String (right click on the Control Flow canvas and click 'Variables') .

2. Create an Execute SQL Task at the beginning of the DTSX package which flows into the rest of the process with an 'on success' connector.

3. On the General tab of the SQLStatement setting in the SQL Task, I put:

select Location + FileName + FileExtension as FileName from Import_File_Specifications where ImportFile = 'xxxx'

'xxxx' is the unique ImportFile key for the file you want to use). Set ResultSet equal to 'Single Row' and SQLSourceType to 'Direct Input'.

4. The next step is to set the Package Variable equal to the filename which comes from the SQL Task. So on the Result Set of the SQL Task, press Add and select Result Name to equal FileName (from the SQL Statement you just entered), and select the Package Variable you created at the beginning.

5. Now, when the DTSX runs the SQL Task, it should set the Package Variable to equal the filename which comes from the database. The final step is to set the ConnectionString of the file's connection manager equal to the Package Variable which contains the filename. Select the connection manager, and on its properties which appear on the right hand side, click the '...' button which appears when you click the 'Expressions' field.

6. When the Property Expressions Editor appears, Press the '...' button to add a new expression property and select 'ConnectionString' from the drop down on the left. Then choose your package variable from the drop down on the right hand side.

That's it! When you test your DTSX package, if you've set up everything correctly then the SQL Task should retrieve the filename from the database, set the Package Variable equal to it and pass that into the Connection Manager.

For an Excel file, you set the 'ExcelFilePath' at runtime rather than the 'ConnectionString' property because the ConnectionString tells the package to use an OLE connection to the file. Note that you must set your PackageVariable to have a default value otherwise the package will not compile.

I admit that this post could have done with more detail, and probably some screen shots but as I said, it's just really a note to remind me of this technique in future. Others have also found different ways of doing this which they might argue are better, but the way I have this set up it works for my application and will allow for different input filenames depending on whether the app is running in DEV, UAT or Production.

Tech Review: Apple iPhone 3GS

It had been a long time since I possessed what could be called a 'good phone'. This is because I always found myself willing to sacrifice having a decent handset for the sake of a free xbox 360, PS3 or Wii with my phone contract.

I finally got out of contract with Orange, and was dying to get a new phone with unlimited net access because it was becoming more important that I have instant access to email on the move. The only two competitors were the Palm Pre...

...and the new iPhone.
Both have a touch screen, both have unlimited data access, both can be used as an iPod (the Pre tricks iTunes into thinking it's an iPod), both have GPS and Wi-Fi and just about everything else you can imagine (see here for a full side-by-side comparison).

But initially, when comparing the two online I couldn't help but be more impressed with the Pre. It looks very sleek, and as if a lot of thought has gone into designing both the hardware and the WebOS operating system on it. It has brilliant multitasking capabilities, lots of ways to intelligently organise your data and do cool stuff with GPS and social networking sites.

That, in addition to the fact that I've never liked the look of the iPhone, meant that for the past couple of months the Pre had been at the top of my wish list.

At the recent WWDC conference in San Fransisco Apple announced the iPhone 3GS, which is basically a pimped out version of the iPhone 3G handset, with a few new features and a faster user experience. It appealed, but still sat second in my wish list when compared with the Pre.

Palm have released the Pre in the US already, but for some reason they have not released it in the UK. I think this is because they haven't completed the compatibility with our slightly different GSM networks.

In the incredibly competitive smartphone market where the Pre is undoubtedly Palm's 'do or die' handset, this is an unbelievable planning error.

There must be literally thousands of people like me, who wanted a new phone and couldn't decide between the Pre and the iPhone. These people would like to see what the Pre can do, but are not willing to hang around for months until Palm get their act together and set a release date. So I lost patience, and bought a new iPhone 3GS last week.

It was disappointing not to have the choice between the two, but if Palm want to compete with Apple, they needed to play the game.

And Palm's move should have been to release the Pre in the UK before the iPhone 3GS, or at least a matter of days or weeks after. Now of course I'm not saying that Palm will fail because I personally did not buy a Pre, but surely it's damaging to their prospective Pre sales that since launch, Apple have sold 2 million iPhone 3GS in the European market alone.

Maybe they underestimated the importance of the European consumer electronics market. But they shouldn't, have because it could have made the difference between the Pre becoming their saviour, or the iPhone becoming their downfall.

The point of this post was really to review the iPhone. And I must say, I absolutely love it. It's a complete pleasure to use, and the hype is correct - the App Store is the killer. So many cool little applications to download, and a large portion of them are completely free. It's not perfect though, the email client is a little overly simplistic and the battery life is horrible. But I don't regret for one second buying the iPhone, and by the time the Pre finally hits the UK shores I will be content that I shouldn't have hung about for months with my old Nokia 6300 and chunky Orange contract in tow.

Tuesday, 23 June 2009

Implementing Error Handling in SSIS

I've been trying to get to grips with SQL Server Integration Services, as opposed to the old DTS technology in SQL Server 2000. It's a task.

One of the things I've found difficult is to implement error handling, and after poring over much rubbish on the net, I found two useful blogs on the subject. Both of them have reusable code which is easily added to existing DTSX packages.

Step one is to read this blog by Jamie Thomson at SSIS Junkie.

Then, this additional blog adds a lot more detail and allows you to store errors in a database table which is very useful if your application has a lot of flat file data inputs (such as a reconciliation).

Now I just need to add error handling which will detect if the file is not there at all, and log that too!

SQL Server Reporting Services and IIS: Failed to access IIS metabase - Guaranteed Fix

I spent ages trying to get SQL Server 2005 Reporting Services working, and kept getting the error "Failed to access IIS metabase" from IIS.

After spending ages trawling through documentation, Microsoft KB articles, messing about with security settings and adding Windows and domain users to the IIS metabase, I found the answer in this post which worked perfectly and took less than a minute. If you're having a similar problem, this will solve it:

My MCP: Failed to access IIS metabase.

To summarise his post, you run this command:

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

What's the lesson? Ignore Microsoft and listen to the blogosphere.

Also: if you're just getting started with Reporting Services, this installation tutorial is the business, and this one is superb for showing you how to build and deploy reports.