SSIS Talk

In short, avoid using mapped network drives inside your SSIS package for things like file paths, control flow settings, log file locations, etc… Instead use UNC path notations. (\\server\share\directory)

Why? Well, if you’re always running an SSIS package by hand using dtexec.exe, or through Business Intelligence Development Studio (BIDS), dtexecui.exe, etc… you don’t really have to worry about this. However, if you’re running the package via a scheduler such as SQL Agent, you’ll run into perceived permission problems, or reports that files cannot be found. The issue with schedulers such as Agent is that they run under a “service account” that does not have access to mapped network drives.

Mapped network drives are set up for your user-session when you log on to a computer. Even if you log in with the same account that the SQL Agent service uses, the service will still not have access to any drives you map because the service does not have access to the drives in *your* session.

This is a common issue that I see with users who are trying to repoint the default behavior of SSIS to a network share, or that are trying to read/write files on a share.

For those of you that have access to the SQL Server 2008 distribution via the various channels, please pay attention to the Microsoft KB article which will likely apply to your installation experience until Visual Studio 2008 (Orcas) SP1 has shipped: http://support.microsoft.com/kb/956139

Basically, if you want to install SQL Server 2008, go ahead. If you want to install SSIS, SSRS, SSAS, and a few other tools, you’re going to have to wait until VS 2008 SP1 has shipped and is installed on your machine before going through the SQL Server 2008 setup. Also note, that the warning in the KB article appears quite late in the SQL Server 2008 setup process, so you have been warned!

Some of you may have visited the link to Microsoft’s whitepaper on the BI Metadata Toolkit only to find the referenced samples download link is not working. Many of us have always had a copy of the whitepaper, but not the samples download. We’ve received that missing download and are sharing it here. The folks at Microsoft are aware of the bad samples download link and are working to get that resolved.

The whitepaper focuses on sharing metadata between Microsoft’s BI tools and is a very good read. It touches on what is “metadata,” how can you use it, where to use it, and most importantly *why* you’d want to use it. Sarbanes-Oxley is mentioned and is important to consider when deploying SSIS solutions. Sarbanes-Oxley (a.k.a. the knee-jerk reaction to the Enron debacle), while broad in nature, basically boils down to enforcing accountability and separation of duties. The BI Metadata Toolkit can help in ensuring SOX compliance.

As part of the samples download, Microsoft includes the following tools:

• Dependency Analyzer: loads into a database lineage data between SSIS, SSAS, and SQL Server
• Dependecy Viewer: graphically see dependencies from the above tool
• Data Source View: A DSV for use in Reporting Services (SSRS) to view dependency repository
• Lineage Repository: A database called SSIS_META that can be used to house metadata from nearly any system
• Reports
• Report Model: For use with Report Builder to allow creating ad-hoc reports
• Integration Services Samples: Sample packages to start auditing and viewing lineage on

BI Metadata Toolkit Whitepaper (.doc - 994 kb)
BI Metadata Toolkit Samples (.msi - 311 kb)

The latest and greatest public CTP of SQL Server 2008 has been released. Visit http://connect.microsoft.com/sqlserver to get it.

Also, when visiting, please enroll in the Bug Bash contest for your chance to win, among other prizes, an Xbox 360.

Matt Masson from the SSIS team has posted some new/improved features of SSIS in SQL Server 2008. Check it out here.

A bit late in reporting this, but Matt has added a part two: http://blogs.msdn.com/mattm/archive/2008/01/22/what-s-new-in-sql-server-2008-for-ssis-part-two.aspx

So, one of the pain points in SSIS is that the Lookup Component performs CaSE sensitive matching, as well as retaining any trailing spaces that may be in the data. So be forewarned that when using the lookup component, it may behoove you to use a derived column upstream to TRIM([SSISColumn]) your data to get rid of any leading/trailing whitespaces and to perform the same (if needed) on the SQL Server side via an SQL statement - ltrim(rtrim(SQLServerColumn)). Also, if you have differing CaSEs among the incoming data and the reference data, you may need to convert one or the other, or both to UPPER/lower case before performing the lookup.

If you’d at least like to see CaSE INsensitive searches, please visit my submission and lets see if we can get this feature added to SSIS in SQL Server 2008.

One workaround for you, should you want to use it, is to use the Fuzzy Lookup Component instead. This has its own problems, as I’ve made mention to in the above feature request submission.

Edit: Jamie Thomson submitted an identical request before me but I’ve made reference to that submission in mine. The idea was to get it in SSIS 2008. Never-the-less, Michael Entin claims this feature request has been dropped due to lack of time.

The November CTP (CTP5) has been released. Go get it and start looking into some major new features added to this release.

Officially, the only change for SSIS are to the lookup components. I’ll have to get my hands dirty to see if there are any other changes that they didn’t comment on. One thing to note, is that C# is the default language type for SSIS Script Tasks/Components in SQL Server 2008.

I’m looking forward to some non-SSIS changes, though, like T-SQL IntelliSense, Transparent Data Encryption, and FILESTREAM support.

Many users have issues when trying to use a stored procedure in an OLE DB Source component. Here are some tips:

• Ensure that there is a final select statement at the end of the stored procedure to ensure the correct resultset is returned
• At the top of the stored procedure as part of the procedure code, add the following line: SET NOCOUNT ON
• At the top of the OLE DB Source SQL statement and before you issue the EXEC YourSproc line, add the following: SET FMTONLY OFF.

This should ensure that when you click on the column mapping tab, the columns are correctly displayed.

Also another approach is to use a table variable in the stored proc. Then at the end, select from that table variable.

Jamie Thomson has posted four SSIS lookup component feature requests that have been recently closed as “fixed” by Microsoft. Hopefully these will make it into the next CTP of SQL Server 2008. Take a look because they all have very high potentials to increase your productivity and useability of SSIS.

See them here: http://blogs.conchango.com/jamiethomson/archive/2007/08/21/SSIS_3A00_-Big-improvements-to-Lookup-in-SQL-Server-2008.aspx

Over here, Oren posted a list of 15 issues with SSIS. Some are founded, most are not. Read on for more:

(more…)