Feeds:
Posts
Comments

Archive for January, 2015

An example of a SOAP XML message containing 2 namespaces is shown below. I want to get the value of the SupplierName from this XML stored in an SQL database table named XMLMessageSource (MessageID int, XMLMessage xml). SOAP_XML_Message_Example

The following query is used to get the SOAP version and Message Body using the namespace of http://schemas.xmlsoap.org/soap/envelope/

;with xmlnamespaces(http://schemas.xmlsoap.org/soap/envelope/’ as [soap])

select distinct XMLMessage.value(‘(/SOAP_Domain_Msg/Context/SOAP_Version)[1]’, ‘nvarchar(max)’) as SOAP_Version                           ,XMLMessage.value(‘(/SOAP_Domain_Msg/Body)[1]’, ‘nvarchar(max)’) as MessageBody

from XMLMessageSource

XQuery1

The namespace http://XYZ.com/ESB/1 is used to get the item of the message body. This ns in the ns:SupplierItem tags refers to this namespace. To get the value of the SupplierName, use the XQuery value function. Note that a *: has been used instead of an individual namespace such as ns.

;with xmlnamespaces(http://XYZ.com/ESB/1’ as [ns])

select distinct

CompanyCode = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:CompanyCode)[1]’, ‘nvarchar(max)’) ,SupplierName = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:SupplierName)[1]’, ‘nvarchar(max)’) ,QuantityAccepted = XMLMessage.value(‘(/SOAP_Domain_Msg/Body/*:DeliveryDockets/ns:QuantityAccepted)[1]’, ‘float’) from #temp

XQuery2

Read Full Post »

In the fuzzy lookup transformation, I have set it to generate a new index, store it and maintain the stored index.

fuzzy_lookup

When I reran my SSIS package, it threw an error below:

[Fuzzy Lookup [462]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80040E14  Description: “A .NET Framework error occurred during execution of user-defined routine or aggregate “sp_FuzzyLookupTableMaintenanceInstall”:
System.Data.SqlClient.SqlException: Maintenance trigger already installed on this reference table.

To resolve this, recreate your destination tables used to store the output of the fuzzy process and drop all tables that were generated during the fuzzy process into the SSIS control flow task.  Also make sure that a TABLE is used as the reference table not the VIEW even though it allows you to select a view. If you still get the same error, rename the index and rerun the package.

The script below drops all tables that were generated by the Fuzzy Lookup stored procedures:

DECLARE @SQLTable AS TABLE
( i   INT
,SQLStatement NVARCHAR(MAX)
)

INSERT INTO @SQLTable
SELECT i = ROW_NUMBER() OVER (ORDER BY (‘DROP TABLE ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME + ‘]’))
,’DROP TABLE ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME + ‘]’ AS SQLStatement
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN (‘dbo’)
AND TABLE_NAME LIKE ‘%FuzzyLookupMatchIndex%’
OR TABLE_NAME LIKE ‘%tg_DataCleaningMaintenance%’
DECLARE @i AS INT = 1
,@RowCount INT
,@SQLStatement AS NVARCHAR(MAX)

SELECT @RowCount = COUNT(*)
FROM @SQLTable

WHILE @i <= @RowCount
BEGIN
SELECT @SQLStatement = SQLStatement
FROM @SQLTable
WHERE i = @i

SET @i = @i + 1

PRINT @SQLStatement

EXEC sp_executesql @SQLStatement
END

An example of the fuzzy lookup data flow in SSIS

fuzzy-lookup-setup-in-ssis-2016

Read Full Post »

%d bloggers like this: