For folks that come across this post...

NZ() function is not available outside of Access so you can't use it in SSIS. What I came across was having to do something like this:

SELECT IIF(ISNULL(Column1),'',Column1) AS MyColumn

I found the above by falling over this MSDN forum post[/url] from Google.

Caveat: I am working with SQL Server 2008 R2 so I have not tried this on SQL Server 2005.