April 23, 2009 at 11:36 am
Hi,
I am having a problem with the XML Data Source component for a Data Flow Task. I have an XML file which I connect to in the design time then I use the "Generate XSD" button to generate an XSD file. The XSD evaluates some of my elements as integers, strings, and dates. The problem I am having as many others have seem to have had in these forums, is that SSIS defaults the string values as DT_WSTR. I have tried to editing the External Metadata through the Advanced Editor option of the component. But, it would not allow me to change them. For instance, I have an XML element called "firm" which has a XSD definition of xs:string. The default shown on the Advanced Editor for this column is DT_WSTR (255). I go ahead and change it to DT_STR, which it then defaults it to 50 size, which I subsequently change to a size of 3. Unfortunately, this it will not let me save any of the changes but refreshes them back to the default. I also tried to change the Output Column to a DT_STR but when I do that a error message box pops up indicating a message something like "Invalid Property!".
Is it possible to change the datatypes in the XML Data Source? Or am I required to use a data conversion? Does the XML Data Source only allow strings to be imported as DT_WSTR?
Sidenote: I even attempted to add to the XSD encoding="windows-1252" to force the document to be ascii but that didn't seem to work either.
Any help would be appreciated. If you need more info let me know. Thanks in advance for the help.
April 23, 2009 at 12:51 pm
Have u tried changing the metadata in the flow?
April 23, 2009 at 12:52 pm
sorry, wrong post.
April 28, 2009 at 7:53 am
No one has provided an answer to my post, but I was able to get my package to run and insert the data in to my table. I don't know if what I did is the way you are supposed to handle an XML Source, but I will explain the way I handled it.
I wasn't able to figure out how to change the DT_WSTR data types on the XML Source component so I used the Derived Column component to make the change instead. I could have used the Data Conversion component to just make the change from DT_WSTR to DT_STR, unfortunately, several of the elements in the XML document contained whitespace or an empty string which required me to use expressions to handle. Rather than split up some of the columns between the different components I just used the Derived Column component for all of my conversions and expression handling.
An interesting thing I found when testing the different transform components was that the Data Conversion component only allows you to create a new column when performing the conversion. When using the Derived Column transform you have the option to replace an existing column or add a new column to the data flow. It appeared to me that you could not use an existing column to change the data type, because when I tried to change one of my columns from DT_WSTR to DT_STR it would not allow me to change the data type if the replace existing column option was chosen.
To summarize, when I used the XML source all of the string columns created from the source had a data type of DT_WSTR. I used the Derived Column transform to handle the whitespace and convert the columns. Then I imported the data through the OLE DB Destination component.
Maybe someone else has a better way or an alternative to share. If not, I hope this helps someone.
July 3, 2009 at 4:46 am
I have had various problems with data types when importing XML. The generated schema doesn't always have a data type generated for each tag so you need to edit the schema manually. I found the simplest way was to import the XML into a table and work from there. All the data is treated an nvarchar on import.
January 2, 2010 at 2:38 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply