Query String Parameters in the BizTalk REST / WCF-WebHTTP Adapter

I was banging my head against my keyboard (metaphorically, but almost literally) trying to get query string parameters to work properly with the WCF-WebHTTP Adapter in BizTalk 2013 R2. I needed to call a REST service using query string parameters in the URL. Using “?param1=val&param2=val” notation when configuring the Send Port was not working.

Fortunately, our good friend in the integration community, Richard Seroter, has encountered this before and blogged about it! Here’s the link.

http://seroter.wordpress.com/2013/03/19/yes-richard-you-can-use-ampersands-on-the-biztalk-rest-adapter-and-some-asp-net-web-api-tips/

The gist is that you have to use the HTML encoded value for the ampersands such as “?param1=val&param2=val”.

Using the tips in this blog post got me up and running. Thanks Richard!

XPath to return default value if node not present

I had to put this into use today in a BizTalk map (inline XSLT). Since this is in BizTalk, I was limited to XPath 1.0 as a solution.

http://stackoverflow.com/questions/4489976/xpath-to-return-default-value-if-node-not-present

This is the problem/solution from StackOverflow:

Problem

Say I have a pair of XML documents

<Foo>
<Bar/>
<Baz>mystring</Baz>
</Foo>

and

<Foo>
<Bar/>
</Foo>

I want an XPath (Version 1.0 only) that returns “mystring” for the first document and “not-found” for the second.

Solution

In XPath 1.0, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz)))

If you want to handle the posible empty Baz element, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz[node()])))

With this input:

<Foo>
<Baz/>
</Foo>

Result: not-found string data type.

This worked beautifully!

SSIS : Creating an error output from a script component

I had to implement this today…

http://colinkirkby.blogspot.com/2007/01/ssis-creating-error-output-from-script.html

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:

http://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/

Referencing this tool:

http://dfld.codeplex.com/

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!

Enjoy!

Fun with SSIS and {GUID’s}

This blog post saved me from many bruises to my forehead from banging it on my desk.

http://beyondrelational.com/modules/2/blogs/66/posts/9927/fun-with-ssis-and-guids.aspx

Handling uniqueidentifier data types in SSIS is not as intuitive as it may seem. This helped me out a lot!

Convert Comma Separated String to Table : 4 different approaches

More SQL Server work of late…this came in handy yesterday!

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

I am grateful for the interwebz!

How To Set and Use Variables in SSIS Execute SQL Task

Needed this today…

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:

http://stackoverflow.com/questions/15372396/ssis-lookup-transform-fails-on-date-column

SSIS 2012 – Package Configurations Menu Option Missing

I am posting this because I came across the same thing. As the original author states – maybe it will be useful for someone else.

SSIS 2012 – Package Configurations Menu Option Missing

Happy coding!

64 bit Microsoft SQL Server Data Tools for Visual Studio 2013

I was trying to install SQL Server Data Tools for Visual Studio 2013 on a Windows 2012 R2 machine. I kept getting an “Invalid Architecture” error. I assumed this was because the install is x86 and the operating system is x64.

This strange, and perhaps even silly, trick got things moving along…

http://social.msdn.microsoft.com/Forums/en-US/4ae33edc-c1ad-44df-bc74-ffb30ea91287/64-bit-microsoft-sql-server-data-tools-business-intelligence-for-visual-studio-2012-?forum=sqlreportingservices

Go figure!