SSIS Talk

I just wanted to help dispell the rumors that some have about the Bulk Insert task in SSIS. This task is NOT marked for deprecation. It will be around for quite a while. At this time, rest assured that if you are using the Bulk Insert task, you will be supported for quite some time.

Please visit http://www.codeplex.com/SQLSrvIntegrationSrv/ for a nice collection of SSIS samples ranging from XML destinations to BizTalk integrations to EzAPI. Please be sure to visit the Integration Services CodePlex site if you haven’t already.

Thanks to my friend, Doug, we now know the top list of SSIS knowledgebase articles! Thanks Doug!

http://dougbert.com/blogs/dougbert/archive/2009/03/17/find-the-top-knowledge-base-articles-for-ssis.aspx

Fellow MVP Aaron Bertrand has blogged about the MRU (Most Recently Used) server list in SSMS under SQL Server 2008 and how hard (and unsupported) it is to clean it up versus how it was under SQL Server 2005. Please give that a read and head over to Connect to vote for his suggestion as you see fit.

(it’s easy [but still undocumented] under SQL Server 2005 - just delete the mru.dat file…)

Connect item: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=424800

Just a quick tip… If you’re having issues debugging the Execute Process Task, ensure that you have a variable created and mapped to each of the process output properties of the Execute Process Task. That is, the StandardOutputVariable and the StandardErrorVariable properties. The StandardOutputVariable will capture any standard output of the process (the text output normally displayed on the screen). The StandardErrorVariable will capture any error output from the specified process.

Doing the above and then setting an OnPostExecute breakpoint on the Execute Process Task, you can use the Locals window to inspect the value of the variables after the process was executed, but before SSIS clears out their values once you’re done debugging.

I know, this isn’t “breaking” news or anything, but what is new is the white paper detailing how Microsoft was able to achieve this record breaking speed using SSIS. Check it out below as its a very interesting read, and it may help generate some new ideas for your implementations.

http://blogs.msdn.com/sqlperf/archive/2009/03/03/an-etl-world-record-revealed-finally.aspx

Simon Sabin, fellow SQL MVP, has posted on the topic of formatting SQL.

For me, I align my queries vertically like so:

select track,
       level,
       title,
       Name,
       ss.length 

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId 

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4 

 order by length,
          title,
          cs.SessionId desc

If I have multiple join predicates, I continue the indention pattern:

select track,
       level,
       title,
       Name,
       ss.length

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId
   and (ss.approved  = 1
        or ss.track  = 'DBA')  

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4

order by length,
         title,
         cs.SessionId desc

What is your SQL formatting style?

I had a little “snafu,” if you will, regarding my domain name provider which caused this site to be unavailable for a few days over the weekend… This has been resolved….

Sorry for the inconvenience!

Happy New Year to all of the readers of SSISTalk.com!!

You can look forward to reading about the SSIS API using real world examples, a review of Itzik Ben-Gan’s new book, “Microsoft SQL Server 2008 T-SQL Fundamentals,” more blog posts about tough SSIS concepts, news from the dev team (that I can share!), and much more! Stay tuned!

From the SSIS development team, Matt Masson has posted a few blog posts on how to use the SSIS API. The posts use references to the 2008 version of SSIS, but to modify them for 2005 requires a simple change in most cases - Upgrading custom SSIS 2005 components to 2008.

You can find the blog posts here: http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

Also, there is a post on using a new API framework for SSIS 2008, titled EzAPI: http://blogs.msdn.com/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx

I haven’t looked into the EzAPI yet, but it certainly sounds interesting.

Let me know what you think about the new posts and if you’d like to see any thing else from the dev team and I’ll pass it along.