Slow Progress

I spent a frustrating morning with a client recently trying to use a Progress database as the source for an Integration Services package. The fix, as is so often the case, was simple, but I figured it was worthwhile documenting it here. I'm afraid that it's a more technical post than I usually put here, but if I can save someone else the same trouble we had then it'll be worthwhile.

The problem was as follows: we were trying to automate importing data from a Progress database to an MS SQL database using an SSIS package built on a 64-bit Windows 7 machine. We had an ODBC link set up (using the 32-bit OpenEdge drivers) that appeared to be working fine for the Import/Export Wizard and source previews and the likes - we could see and query data - but failed with ODBC error messages when run as an SSIS package from within the Business Intelligence Development Studio. "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

Google searching the problem wasn't very encouraging. We tried a dozen different workarounds, from editing the connection strings to bypass the ODBC DSN, registry hacks, trying to fake a 64-bit ODBC connection, but nothing helped. Then, as sometimes happens, I remembered a setting in BIDS that I'd seen a long time ago, never used and then forgot about.

In BIDS, select "Properties" from the "Project" menu. Select "Debugging" on the left-hand side of the Property Pages dialog, then change the value of Run64BitRuntime under "Debug Options" to "False". That simple. Once I'd done that, the import package ran like a train.

Read more »

Looping through SQL objects with SSIS

A lady named Barbara wrote this week to say that she'd enjoyed my SSIS Foreach Loop article, but she wondered how one might go about using the Foreach Loop container if the collection was made up of SQL tables instead of files. The procedure is quite similar; the difference lies in using the very useful Foreach ADO.NET Schema Rowset Enumerator to enumerate tables for a given schema.

I figured that the answer to her question, or one possible variant at least, was worth posting on the site, so here it is (PDF, 483KB).

Forty-four thousand satisfied customers

A couple of years ago, I wrote an article for SQLServerCentral.com that described how SQL Server Integration Services could be (mis)used to query a SQL database, format the query results as HTML, and automatically e-mail them on to interested parties. It was more of a demonstration of what were then relatively poorly documented features of SSIS than a serious attempt to recreate a reporting solution, but it touched an unexpected chord and got a huge response on the site. It's been a featured article three times, been included in the publication "Best of SQLServerCentral Volume 6", and has a maximum five-star rating from no fewer than 44,550 readers as I write this.

All this blowing my own trumpet is well and good, but up to now this piece was only visible to registered visitors of SQLServerCentral.com. Today, however, in a shameless attempt to drive some traffic to my refurbished and improved website, I've posted it as a PDF on my own site for the enjoyment and enlightenment of one and all. It's a bit dated by now, and I can certainly think of easier and more efficient ways of achieving the same ends than by using SSIS, SQL XML queries and XSL transformations, but if that's your idea of fun then please take a look.

Crystal Ball

Firstly, a happy new year to everyone. I'm really looking forward to getting to grips with this year's work and challenges, and I hope that you can say something similar.

There's a major new release of SQL Server in the pipeline, for starters. The preview release has been available for some months now, and it already seems like being the most significant product version since the landmark 2005 release. (It's no accident that SQL Server 2005 is still by far the most popular rendition of the Microsoft DBMS that I come across at customer sites to this day). SSIS functionality gets a long-overdue update in 2011 that introduces important new capabilities to the suite, as well as major updates to the DB engine querying capabilities, spatial data, high availability among other features.

Something to look forward to, then. 2010 was a quiet year on the MS database front. There's certainly been a large push on from Microsoft in the direction of the cloud, with SQL Azure making huge strides in a short time as regards functionality and ease-of-use. While cloud database services have the possibility to completely change the way we develop and interact with database architecture, it remains to be seen whether businesses (in the short-to-medium term) will want to surrender their data to an external entity. Regardless of your point of view on the morality or legality of the WikiLeaks affair, Amazon's actions in closing infrastructure access to the organisation highlights one of the inherent weaknesses of cloud computing; your business is dependent not only on the professionalism and technical foundations of external parties, but also on the goodwill of external governments and other agencies. Would you host your online bookstore database on Amazon Web Services? Your online presentation app with Microsoft Azure? Your mapping technology with Google? The technical barriers to cloud application and data delivery have practically disappeared already, but questions regarding ownership, permissions, entitlements and responsibilities arise that were never an issue in a world of applications and data hosted on private data centres.

It promises to be an interesting year.

Executing a list of SQL scripts using SSIS

The redoubtable Jamie Thomson has made a simple and clear video demo of how SSIS can be used to execute a number of SQL scripts automatically. You can find the video here.

Fame at last

Let's get this started by blowing my own trumpet: an article about mailing from SSIS that I submitted to SQLServerCentral.com almost a year ago was republished last Friday and appeared at the top of the home page. It's been read over 7,000 times in the past three days and almost 20,000 times in total as I write this, and the reactions I've received have been extremely positive.

It's only now, in 2009, that you're starting to see a serious amount of information and background on Integration Services. It's been languishing in the background since the launch of SQL Server 2005, largely, as far as I can see, because the documentation was so sparse. It was easy to be an SSIS expert up to 12 months ago, but now I have to raise my game and get to grips with the enhancements to SSIS in SQL Server 2008.