SSIS : Creating an error output from a script component

I had to implement this today…

I have a script component in SSIS that could have exceptions that need to be handled gracefully, row-by-row, and logged as such. This blog post contained all I needed!


SSIS DataFlow Discoverer

I have a pretty large ETL project I’m working on. I was getting the generic (read unhelpful) error message when records were not writing to my target database. It told me that there was a constraint violation, and gave me a column number.

Mind you, this isn’t the number corresponding with the column in the database. Rather, it was a number corresponding to a derived column in the SSIS package. Ugh!!! How was I to know what column it was? Enter the SSIS DataFlow Discoverer.

I found this blog:

Referencing this tool:

After running the tool on my DTSX file, I was able to use the column number in a query against the database the tool generated and, voila! It helped get to the column in question much more quickly than having to do it manually by process of elimination!


How To Set and Use Variables in SSIS Execute SQL Task

Needed this today…

How To Set and Use Variables in SSIS Execute SQL Task

We are doing some ETL work into a data warehouse for a client. One of the dimensions has a “smart key” value for a date dimension. So, we figured we could forego looking up the date key to populate an associated fact with and instead transform the incoming date to the smart key value. This worked great…except when it didn’t! Specifically, when we ran across a date that exceeded the data in the date dimension table. Yuck!

The solution was to do an initial lookup of the max date in the date dimension and capture that into a variable. Then, in the derived column where we were doing the smart key transformation, we would also check to see if the date exceeded the max in the dimension. If it did, we would set it to the default key value.

Happy coding!

SQL Server 2014 – SSIS Lookup Transform Fails on Date Column (datetimeoffset)

This came in handy today. We had an issue where we had tested a SSIS package in our development environment. When we deployed it to QA, we received an error:

VS_ISBROKEN – column [x] and column [y] have incompatible types

This is on SQL Server 2014. We confirmed that the dev server was on a slightly older build than the qa server. What fixed the issue was changing the provider on the connection string for the package to SQLNCLI11.1 instead of SQLOLEDB. The data type in question was a datetimeoffset.

This is the link where we came upon the solution:

Caching Values from the SSO Configuration Store

Most of us who have been using BizTalk Server for a while use the SSO database as a secure store of configuration values. Microsoft made this much easier a few years ago when they released the SSO Configuration Application MMC Snap-In. Better yet, it comes with a sample class you can use to implement in your applications. This frees developers from having to add AppConfig segments to the BizTalk config file. In multi-server deployments, this gets really nasty because you have to change the config on every application server. SSO is by default available to the cluster. So, configuration changes are automatically made available to the entire BizTalk Group.

One common use of the SSO Config Store is a repository for database connection strings required by BizTalk maps performing database look-ups. A script component can call out to a class which wraps the client code that ships with the MMC Snap-In. Or you could use the BizTalk Mapper Extensions created by Sandro Pereira, which ship with a custom functoid to SSO config.

However, there is a problem. When you use the tool in a looping BizTalk map, it will make a call to the SSO database to retrieve config values for each element in your loop. In the case of a connection string, you are making unnecessary calls to SSO. Just open up the SQL Server Profiler, and you can watch all of the extra calls to the SSO database. With large messages, I have seen this severely degrade performance.

I have opted for creating a simple wrapper method, which uses a module-level dictionary object to cache configuration values needed by the map. This reduces the number of calls to the SSO database to one per config value. As you might expect, this significantly increases performance with even moderately large messages. The durability of the cache is only for the life of a single instance of the map. So, if you want something even more durable, you would need to rely on something like the AppFabric Cache.

Here is some sample code for caching values from the SSO config store…

private Dictionary<string, string> _ssoConfigurationValues = new Dictionary<string, string>();

public string GetSSOConfiguration(string appName, string propName)
string key = string.Concat(appName, propName);
string value = string.Empty;

if (_ssoConfigurationValues.ContainsKey(key))
return _ssoConfigurationValues[key];
value = SSO.Utility.SSOClientHelper.Read(appName, propName);
_ssoConfigurationValues.Add(key, value);
return value;

catch (Exception ex)
throw new ApplicationException("GetSSOConfiguration function call read failed", ex);

Handling “OR” Operators in the BizTalk Rules Engine

Once again, here we are talking about the BRE!

I have a set of rules that utilize the OR operator. I found a very, very interesting article describing in detail how the BRE handles the OR operator.

Cliff Notes:

  • OR is not handled like procedural languages, such as C# – there is no short-circuiting. All elements of the OR condition are evaluated, and therefore each submits a separate match to the Agenda to be followed by the BRE
  • In the BRE (or any Rete engine) OR is much more analogous to SQL. Since each member of an OR condition results in its own “record” per-se, it can be loosely understood as a SQL using the UNION ALL clause, having a separate SQL statement for each member of the OR condition
  • BizTalk BRE performs a de-duplication after processing rules that keeps the multiple Rete results from rules using OR from being added to the processing Agenda. This mitigates some of the, “What the…” you felt when reading bullet one above!
  • If you purposely want separate Agenda items to be processed, you can force the BRE to do this by creating a separate rule for each member of your OR condition (i.e. don’t use OR in your rules, create multiple rules instead).

Save Whole XML File in SQL Table Field

I worked on what ended up being a surprisingly interesting prototype to answer a question in


 I want to save whole XML message in sql table field which is with datatype XML(.).. how can i achieve this.


I did a prototype to do what you are asking. I actually haven’t inserted into the xml data type in SQL Server before, so I was curious if that added any complexity. I’ll just go through the mapping pieces and assume that you can easily find documentation on how to insert data into SQL Server using the Add Generated Items –> Consume Adapter Service function.

The interesting piece was writing the map. I used a simple PO schema. My sample table was XmlRepository with a ID column, RepositoryID, and the RawXml field.

The script is an inline XSLT script that is as follows:

<xsl:element name="ns3:RawXml">
<xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
<xsl:copy-of select="/" />
<xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>


The question right off is why the CDATA? Well, since the value of the RawXml element that needs to be inserted into SQL Server is XML data, it has to be wrapped in a CDATA tag. Otherwise, the XML data gets validated and will not properly make it to the SQL Server. You can try it and see what I mean. Don’t worry, the CDATA gets stripped before the XML gets written to the table.

The interesting pieces are the xsl:text elements, which manually create a CDATA wrapper for the XML that ends up in the RawXml field. Apparently, the normal Grid Property for creating CDATA output doesn’t work in conjunction with the xsl:copy-of element. That tripped me up for a while!

What the map outputs is a document that looks like the following:

<ns0:Insert xmlns:array="" xmlns:ns3=""
				<![CDATA[<ns0:PurchaseOrder xmlns:ns0="http://XmlRepositoryDemo.PurchaseOrder">


The map can be called from a simple Orchestration, or even a messaging-only scenario, and sent across the wire in a request/reply as you would for normally sending data to SQL Server.

I hope this helps someone. It ended up being more than I expected!