Sep
1
2010

Making SSIS work with the 64-bit Excel (ACE) provider

I've previously blogged about the challenges of the new 64-bit ACE Provider that includes the Excel driver, since the 32-bit and 64-bit versions cannot be installed side-by-side. In this detailed post that includes screen shots, Hrvoje Piasevoli from Croatia describes 2 approaches for working with the 64-bit driver from SSIS: Importing data from 64bit Excel in SSIS Since the author's 2 workarounds are (1) the Import and Export Wizard and (2) OPENROWSET, not all users will find them completely satisfying. A ... [More]
Jun
11
2010

Top Connect issues recently for SSIS

This afternoon I was testing to see whether I had VSTS installed correctly, and I clicked on one of my saved queries for Microsoft Connect items related to Integration Services. Whoa! People are NOT happy about the incompatibility with Visual Studio 2010 that Jeff Bernhardt recently addressed on the team blog! In fact, it looks like there were 97 things that people haven't been happy about in the last 90 days. Here are those 97 things, sorted in descending order by number of votes. I was going to ... [More]
Apr
12
2009

No need to call Support for these common challenges in SSIS

My hard-working teammate and fellow technical writer, Carla Sabotta, recently scanned the records of an eye-crossing 900 Microsoft Support incidents involving SQL Server Integration Services 2005 and 2008. She was looking for issues that the customer could easily have answered with nothing more than the right Help topic from SQL Server Books Online. The percentage turned out to be quite small. I'm not certain how to interpret this small number, but these seem to be my options: "Glass is half emp... [More]
Mar
17
2009

Find the top Knowledge Base articles for DTS

Here are the top Microsoft Support Knowledge Base articles that SQL Server Data Transformation Services (DTS) customers have been reading. You may find them useful also. This time I've simply sorted the list from most viewed to least view, for the cumulative timeframe of FY06 through the incomplete F09. Yet one more big thank-you to my buddy Jason Howell, the SSIS guru in Microsoft Support, for sending us this additional list. KB No. Title 269074 INF: How to Run a DTS Package as a S... [More]
Mar
17
2009

Find the top Knowledge Base articles for SSIS

Here are the top Microsoft Support Knowledge Base articles that Integration Services customers have been reading. You may find them useful also. The numeric columns in the following table represent rank in the indicated period by number of page views: "1" means that KB article got the most hits a higher number means it got fewer  0 (zero) means it didn't get any. I have sorted the list on the Total column from most viewed to least viewed. This list does not distinguish between SQ... [More]
Mar
16
2009

Solve some common pain points in BIDS

Here are a few suggestions for some of the things that may be tripping you up while you're working on SQL Server Integration Services packages in Business Intelligence Development Studio (BIDS). Thanks to my buddy Jason Howell, who's an SSIS whiz in Microsoft Support, for sending us this list recently. 1. Make that "Visual Studio is busy" message go away! Validation happens often in BIDS and can be slow, especially if you're connecting to slow or unavailable data sources. Possi... [More]
Feb
13
2009

Exporting data from SSIS to Excel 2007: Gotchas

The default Microsoft Excel 2007 file fomat, with the file extension .xslx, is in fact an XML file, or rather a Zip file that contains multiple XML files. However you can also save an Excel file in binary format, with the file extension .xlsb. For more information on Excel file formats, see File formats that are supported in Excel. Our SQL Server Books Online topic, How to: Connect to an Excel Workbook, contains incomplete and potentially misleading information when it tells you to set Extended Properties ... [More]
Jan
19
2009

Scan the list of Microsoft Support articles for Integration Services

If you've had a seemingly obscure problem with Integration Services, you may not be alone! Microsoft Support may already have explored it and documented it in a Knowledge Base article. Here's a list of the published KB articles for SSIS, both 2008 and 2005. A big thanks to my buddy in Support, Jason Howell, invisible and tireless hero of SSIS, for the queries that helped me to produce these lists quickly. Keep in mind that you can use a free service like KBAlertz to stay up to date on newly-rele... [More]
Aug
20
2008

Excel, the Office 12 ACE Provider, and performance

Recently an internal Microsoft team ran into performance issues when they switched from using the Jet Provider and writing out Excel 2003 files, to using the Office 12/2007 ACE Provider and writing out Excel 2007 files from their Integration Services package. Their inquiries brought the following information to light: There are 3 reasons for reduced performance with the Office 12 ACE Provider when used with Excel 2007 files, either binary (.xlsb)or XML (*.xlsx): The ACE Provider ... [More]
Aug
12
2008

Visual Studio SP1 requirement for SQL Server 2008

SQL Server MVP Phil Brammer has already blogged about the fact that, if you have Visual Studio 2008 installed, you have to install VS 2008 Service Pack 1 before installing SQL Server 2008 "Katmai". Why? Because Katmai contains bits and pieces of SP1, and will refuse to install over VS 2008 RTM. (Just to make life more interesting, Microsoft didn't release VS 2008 SP1 until several days after SQL Server 2008.) You can read a good blog post that summarizes the entire issue, and explains the de... [More]