SSIS: "And If You've Made it Wrong..."

When you start working with SSIS, there's one thing nobody mentions: you have to learn a new syntax for expressions. It's not T-SQL, it's not VB.NET, it's not JavaScript, it's not like anything you've made before. It's "SSIS Expression Syntax", and you can learn all about it at Integration Services Expression Reference. (And yes, I'm pretty sure "it won't keep you comin' back for more"!)

I bring this up because until you understand this, you're going to think SSIS was written in FORTRAN, because anytime you have to enter an expression, nothing works. Ever. For example, let's write an expression in T-SQL to strip out all occurrences of the hex 12 (0x0C, "FF") character from a string. Simple, right?

-- T-SQL code.  Don't try this in SSIS. 
DECLARE @FF CHAR
SET @FF = 0X0C
DECLARE @Msg VARCHAR(50)

SET @Msg = 'Hello ' + @FF + 'SSIS'
PRINT @Msg

SET @Msg = REPLACE(@Msg, 0x0C, '')
PRINT @Msg

The output, depending on your font, collation, codepage, which version of Windows you're using, and hat size, will look something like this:

Hello §SSIS
Hello SSIS

So, we've proven we know how to use the REPLACE function in T-SQL. Admirable, but it doesn't help us in SSIS. That is, this doesn't work:

REPLACE(SampleBeg, 0x0C, '')

Try that expression there, and you'll get the usual non-helpful error SSIS message stack. For some reason, SSIS likes double-quotes, not single-quotes, and of course has its own unique way of expressing character constants in hex:

REPLACE(SampleBeg, "\x000C", "")

I'm sure there are excellent reasons why SSIS is such a Frankenstein's monster. When you're building something that might not be such a good idea to begin with, you're going to find some funky design choices forced on you, and my guess is that's what happened with SSIS. All I know is, the way it's been designed, it will be a long time before I can get it to do what I want.

SSIS: Word-wrapping Annotations (Using Only Notepad)

Remember that Twilight Zone episode, where a man sells his soul to the Devil for immortality, only to be sentenced to life in prison the next day?

I'm learning SSIS (2005, for now), and have discovered, like all those before me, that Microsoft implemented Annotations, but... they don't word-wrap! Your Annotation must fit on one really long one line! Bwa-ha-ha-ha-ha-ha!

Of course, I'm no longer surprised by anything SSIS: I even have a bookmark for "SSIS" Google searches now. And that's how I found Paul Blackwell's excellent Hidden SSIS Features: Word Wrapping Your Annotations And More which covers the subject extremely well. I highly recommend the entire article, especially the section titled Transparency & Word Wrapping.

Another very interesting read is [SSIS] Modifier l'apparence des packages via l'API [FAIL] by François Jehl. (We non-Francophones can use Google's translate-to-English, or the page itself has a Microsoft translation button.) After some research, his conclusion is that Annotations are a feature from a product created before SSIS ("DaVinci Design Surface", hence "MSDDS"), which might explain why Annotations are hacked implemented in SSIS package files as XML nested inside the document's XML. (Hmmm... that would explain all the <s and >s floating around!) For what François was attempting, that was a fatal roadblock. Fortunately, we're not trying anything nearly so ambitious or useful as he was - certainly nothing Notepad can't handle!

Anyway, using what they've uncovered, here's how to make your Annotations word-wrap. (This is how I do it - use at your own risk, and make a backup of your package file first). Close Visual Studio, and open the package file in Notepad. Search for controlprogid="MSDDS.Text", and you'll be treated to nested XML that looks something like this:

<ddscontrol controlprogid="MSDDS.Text" left="4118" top="-655" logicalid="18" controlid="9" masterid="0" hint1="0" hint2="0" width="6153" height="994" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
    <control>
      <ddsxmlobjectstreaminitwrapper binary="0002000009180000e20300000300640000000500008008000000000000002a000054006800650001000000900144420100065461686f0074006f002000506d615800540068006500200063006f006e006e0065006300740069006f006e0020006d0061006e00610067006500054006800650007200200066006f0074006f0020005000720020007400680069007300200d61582005400610073006b0020006600610069006c0073002000730070006f007200610064000540068006500074006f00200050006900630061006c006c007900200075006e006c00650073007300200069007400270073002000730065007400200d6158005400680065002006100730073006900760065002000540068006500004d006f00640065002e0000000000" />
    </control>
    <layoutobject>
      <ddsxmlobj />
    </layoutobject>
    <shape groupshapeid="0" groupnode="0" />
  </ddscontrol>

See the red "2" at position 61 of the ddsxmlobjectstreaminitwrapper item? To enable word-wrap, simply OR in a value of 1: in this case, we change the "10" (decimal 2) to "11" (decimal 3). Once that's done, save the package file (you made a backup first, right?), exit Notepad, open the package with Visual Studio, and - viola! - your Annotation is word-wrapped! Now do the same thing for all your other Annotations. (Hey, it's a hack, not magic.)

So, if it's just a bit-flip, why didn't Microsoft simply put a checkbox on the Properties page for Annotations? Because... Annotations don't have Property pages! Bwa-ha-ha-ha-ha-ha!

SSIS FTP Task: Directory is not specified in the file connection manager

Are you getting this error message, been Googling for an hour, and nothing helps?

Error: 0xC0029186 at {Your FTP Task}, {Your FTP Task Description}:
Directory is not specified in the file connection manager {Your Variable Name}.

If you entered the value for the source and/or target filename variable and enclosed it with double-quotes, try removing them. Worked for me.

(Before coming over to the SQL Server side, I was a full-time Windows application developer, working in C, C++, and C#, ever since Microsoft QuickC for Windows was released, way back in 1990. If you've ever wondered what it's like being a Windows application developer, imagine that everything in SQL Server was designed and implemented just like SSIS. If you've ever spent much time writing application code for Windows, you'll know exactly what I mean. If not, count your blessings!)