February 17, 2011 at 5:17 am
Hi,
I running SQL Server 2008 R2 on Windows 2008 R2 and I am trying to use the "FOR XML" command to select data from the column [TestCol1] in the table [TestTable1] which has a [varchar] data type and insert that record into the column [TestCol1] in the table [TestTable2]. I'm trying to execute the process in SSIS.
First, in SSMS I create the two tables [TestTable1] and [TestTable2] and insert the one record into the table [TestTable1] ......
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable1]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable2]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable2]
GO
CREATE TABLE [dbo].[TestTable1](
[TestCol1] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestTable2](
[TestCol1] [xml] NULL
) ON [PRIMARY]
GO
INSERT INTO [TestTable1] VALUES ('Col1Row1Value')
GO
Now that I have the required data, I then create a very simple SSIS package which just has an "OLE DB Source" ([TestTable1]) and an "OLE DB Destination" ([TestTable2]). In the Properties of the "OLE DB Source" SSIS component I've changed the "Data access mode:" to "SQL Command" and then entered the following syntax:
SELECT [TestCol1] FROM [TestTable1] FOR XML AUTO, ELEMENTS;
When I execute the same syntax in SSMS it returns the following:
<TestTable1>
<TestCol1>Col1Row1Value</TestCol1>
</TestTable1>
However, when I execute the SSIS package I get the following errors:
[OLE DB Destination [19]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "XML parsing: line 1, character 26, illegal xml character".
[OLE DB Destination [19]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (32). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
I've tried everything I can think of, and spent sometime on Google as well, but I'm stuck. Any help would be greatly appreciated.
Thanks.
www.sqlAssociates.co.uk
February 17, 2011 at 10:56 am
Disclaimer: this is a workaround for a limitation in SSIS
When you output XML to an OLE DB Source component it shows as a byte array which is difficult (maybe impossible, I could not get it into a Unicode string) to use in a Data Flow task.
Using this query in the OLE DB source component allowed it to pass cleanly into the destination table into the XML typed column:
WITH my_cte(TestCol1)
AS (SELECT [TestCol1]
FROM [TestTable1]
FOR XML AUTO,
ELEMENTS
)
SELECT CONVERT(NVARCHAR(MAX), TestCol1) as TestCol1
FROM my_cte ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply