Developing Matt

My Technical Journal

Archive for the ‘SSIS’ Category

Package Event Handler

leave a comment »

A package level event handler is not just an event handler for the package. It is also an event raised for each executable inside the package.  Restated: if you place an event handler and place it at the top node (at the package level) this will occur for your package and every executable inside of it.

The problem: I thought that I could throw an insert to a table to discover when my package completed. To do this …at the package level……I set the ‘on post execute’ handler to include an insert statement with the end time. However, it inserted twice (when there was only one object). Once for the object, once for the package.

The solution: I created a constraint (from an empty container) with a test on the variable System::SourceParentGuid. When it is empty, I know that it is not coming from a parent executable, and from the package only.  The constraint was @SourceParentGUID==""     Now my insert will only execute on post execute when there isn’t a source parent GUID. 




Written by matt

August 10, 2012 at 8:19 am

Posted in SSIS

Tagged with

SSIS Variables Can be changed in Job Step

leave a comment »

I wanted to make note that if you want to set values to variables one way to do this is via the sql job. You do this in the Set Values Tab (job properties, step properties, set values tab).

Set the property path to your custom VARIABLE_NAME

Set the value to your custom value. The advantage of doing this, of course, is being able to dynamically change a value instead of changing the source. For example, I like to set up email messaging here instead of hard-coding inside the application:
Property Path:

Written by matt

February 21, 2012 at 4:15 pm

Posted in SSIS

Prescedent Constraint Missing Prescedent Constraint

leave a comment »

For some reason my expression on my prescedent constraint wasn’t passing successfully. Restated: I have a constraint on a data flow item to test an expression. I have it testing a string value that I set in the script editor. If it is not empty I send an email. The expression I was using (@[User::EmailRowErrors] !=”” or @EmailRowErrors !=”” Either one was working ) .

For some reason it stopped working. Worked before. Doesn’t work now. After some time I learned that the expression editor cannot handle > 4k characters, so it was just bombing because my text was simply too long. I never received an error of any kind, fyi.

The end result is that I created another boolean variable that I also set in the script editor and I test for it and now it works like a champ. (@[User::SendEmail] ==true) Thanks for reading.

Written by matt

February 21, 2012 at 3:34 pm

Posted in SSIS

32 bit Twice Shy

leave a comment »


If you sql server is running 64 bit and your ssis is using something other than a 64 bit provider you might receive an error with your job. 

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered — perhaps no 64-bit provider is available Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2012-01-18 11:58:16.53     Code: 0xC020801C     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. 

Change the step on your job (the one executing your ssis package) to Use 32 bit runtime (Execution options tab).  Jet is not 64 bit, therefore you have to change your step to run it as a 32 bit application.


Written by matt

January 18, 2012 at 5:37 pm

Posted in SSIS

Sensitive SSIS and Encryption

leave a comment »

If you ENCRYPT SENSITIVE DATA WITH USER KEY everything will work fine until you import and run the job.  You might get:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

If you set it to be a password (ENCRYPT SENSITIVE WITH USER KEY)) and don’t set up that password when setting up the job you will get:

Failed to decrypt an encrypted XML node because the password was not specified or not correct.

Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user .". 

The way I like to do it is to set it at ENCRYPT SENSITIVE WITH USER KEY which will enable the passwords to be saved in the package for development and upon import change the encryption to use Rely on server storage and roles for access control which will use the permissions inherent to the job owner.

New Picture (8)

Written by matt

January 18, 2012 at 5:20 pm

Posted in SSIS

Your job owner owns more than you

leave a comment »

The job owner matters.

When you run your Sql Server job that executes your SSIS package or a job creating files or a job inserting to an access database or a job accessing files you may get something akin to

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

This means that you need to find an account and set the owner of the job to that account.  It could be system or NT AUTHORITY\SYSTEM or whatever your sysadmin dictates. 

You would think that the ‘execute as’ would control who is running the show, but this is not the case.  ‘Execute as’ is just the Carney pulling the levers.

Written by matt

January 17, 2012 at 4:32 pm

Posted in Sql Server, SSIS

Unicorns are MAGIC!

leave a comment »

What is the difference between unicode and non-unicode character types?  Which one should I use? Nvarchar (unicode) or Varchar (non-unicode)?

If you can use non-unicode use it as it will be smaller and quicker (generally 70% smaller footprint), but if you need things like é or support other languages use unicode.

Unicode is the UNIVERSAL encoding system.  This is supported by a non-profit organization (Unicode Consortium) to ensure that we can all understand each other clearly…not just internationally but historically as well.

To help you remember, the N in front of nvarchar or nchar stands for the National language character set (i.e. unicode).  If your data structure might possibly expand to other languages, set it up to use a unicode datatype. 

If you work with ssis at all you have likely seen the error "Cannot Convert between Unicode and Non Unicode String Data Type"
If you are going from non-unicode (varchar or char) to unicode (nvarchar or nchar) you will benefit transforming the data with the  unicode string [dt_wstr] or unicode text stream [dt_ntext] depending on how lengthy the field is.. 

There is no reason for such a silly title or for the picture below except to make you think this post would be something interesting to read.  There is no relationship between unicode and unicorns, nor will there ever be.

Written by matt

January 13, 2012 at 6:32 am

Posted in Sql Server, SSIS