DTS from XML

Does anyone know how to create a dts package that will take a XML document and import the contents to a new table in sql.
I have looked in the tasks of DTS and cannot find the xml connection.
Any pointers to some samples would be great.
ThanksConsider upgrading to SQL Server 2005, which has extended support for XML including data import through the Integration Services application. (Interestingly, Integration Services does NOT export XML. Go figure...).sql

DTS And VBScript

I have an application written in C#, which allows the user to load their destination schema (in xml format) and writer their transformation expressions either in VBScript or in T-SQL. It uses DTS to do the ETL job.

Everything works great except with one problem. If there is a hypen ("-") in any of the destination attributes, the transformation fails with syntax error. It happens only with VBScript.

Here is my transformation:

"Function Main()

DTSDestination(\"Field-1\") = #01/01/2004#

End Function"

If I replace the hypen with underscore, it works without any problem.

Does anyone have any idea about this problem?Did you try to put the field name between brackets?
[field-1]|||Tried with \"[Field-1]\" and it did not do the magic|||Perhaps a hyphen is an invalid character for a field name. Use underscores and your problem is solved. You shouldn't be using '-'s in field names, anyway.|||In general hypen ('-'s) are not allowed in a field. But if you enclose in [], it is a valid chr (atleast in SQLServer!).

As my application is a B2B application and schema is maintained by an organization, I don't have any control. Hence I can not modify the field name.

BTW, I tried the same this with Enterprise Manager, it works.

DT_TEXT not supported while importing from an XML file?

I have an XML file that contains a field that has over 8000 characters in it. I cannot use a (n)varchar for it, I must use a Text for it (although as a last resort I could split the string into several (n)varchar columns). I want to set the external column of the XML column to a DT_TEXT and I receive and error message that states :

Error at Import XFFD Data [xffd [1]]: The SSIS Data Flow Task data type "DT_TEXT" on the external metadata column "reviewText" (32411) is not supported for the component "xffd" (1).

I've tried converting the nvarchar into a text stream with the use of a Data Conversion Transform, but I think the Validating steps are truncating the field.

HELP. I've been beating my head against this for a couple hours a day.

Thanks,
Scott

The XmlSrc only supports Unicode text. Set the output column type of the XML source to DT_NTEXT. If you need it to be DT_TEXT downstream, you would then need to use a data conversion transform to convert to DT_TEXT.

Thanks
Mark