Search code examples
sql-serverssisetlderived-column

URL syntax in SSIS Derived Column

I need some assistance with the syntax of a system URL for a SSIS Derived Column. I have triple checked the package fields CO_ID and ProcessNo, so I must be missing something in the URL syntax.

"<a href=\"" + "https://pub.mickle.com /orders/Subsystem/Utils/RequestDoo.asp?ID=" + ORDER No class="nounderline" title="">" + "Order No" + CO_ID + ProcessNo + "</a>"

Any assistance to guide me into the light is appreciated

Solution

  • There are many issues in this expression:

    • Additional space after the .com domain : https://pub.mickle.com /orders
    • Double quotes escapes missing.
    • Missing Bracket around Columns names (because columns names like ORDER No contains spaces)

    I think you are looking for the following expression:

    "<a href=\"https://pub.mickle.com/orders/Subsystem/Utils/RequestDoo.asp?ID=" + [ORDER No] + " class=\"nounderline\" title=\"\"> Order No" + [CO_ID] + [ProcessNo] + "</a>"

    Also, if [ORDER No], [CO_ID] or [ProcessNo] are numeric columns then you should use CAST operation:

    "<a href=\"https://pub.mickle.com/orders/Subsystem/Utils/RequestDoo.asp?ID=" + (DT_WSTR,50)[ORDER No] + " class=\"nounderline\" title=\"\"> Order No" + (DT_WSTR,50)[CO_ID] + (DT_WSTR,50)[ProcessNo] + "</a>"