SSIS - OLE DB Command DT_NTEXT Output Type and XML input

-
Problem:

I am working on an SSIS data flow as shown in the image below. Here are the details of the flow.
  1. Getting some records.
  2. Adding a dummy column which is a DT_NTEXT type
  3. This an OLE DB command which is executing a stored procedure. The output of the stored procedure is XML but is of type NVARCHAR(MAX). The output is populating the dummy field.
  4. Writing the XML from the dummy column to a table.
When the package is executed, the destination DB only gets populated with a < instead of the full XML. If I change the dummy column to type WSTR, the XML is succesfully written to the table in full. I need to write the XML to an NVARCHAR(MAX) field, as the XML could be large and exceed the limits of the WSTR type.
Does anyone have an idea what is going on and how I can write my XML to an NVARCHAR(MAX) field?
SSIS package

Solution:

After running many experiments and searching over the internet, it looks like this is an issue in SSIS, since OLE DB Command cannot be mapped to DT_NTEXT columns:
As a workaround, you can use a Script Component to get the XML value using a parameterized SQLCommand, and map the output to an output column (no need to create column using a derived column transfomation).

While searching, I found that this was an open issue in the Microsoft forums:

The following feedback was given by Microsoft support team:
OLE DB clients handle XML columns (which are not part of the OLE DB spec, but SQL specific) like they would NTEXT fields. The OLE DB provider for the SQL Task currently does not fully support LOB fields ' if you store the result in an Object variable, it will return a COM object that points to the stream of data, but not the actual results (which isn't very useful).
There are a couple of workarounds for this problem.
1) Cast the results to varchar, and use a String variable
2) Use an ADO.NET connection instead of OLE DB
Considering the amount of work involved in changing/fixing the current behavior, and that there are workarounds available, we've decided not to fix this issue in this release.


Original post: https://stackoverflow.com/questions/58133568/ole-db-command-dt-ntext-output-type-and-xml-input/58158225#58158225

Comments

Post a Comment

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T...
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu...
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin...
Read more