Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: Help importing xml file into sql server table. Need to setup package and transformati

    I need help importing an xml file into a sql server database table.
    ive never done this before and have been trying to get the concept in this article to work, but I am inevitably doing something wrong.

    Loading XML data into SQL Server 2008

    Can anyone point me in the right direction here, please?

    I don't know enough about xml file structures, etc. to know how this works, but Im sure I can learn quick if I can find someone to help...

    If you have some suggestions, i would be most appreciative... thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what problem are you having? An error? Are you stuck on something in particular or do you want some to hold your hand and baby step you through the whole thing. The former you can get help with here for sure. The latter not so much.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Posts
    16

    ouch...

    Thanks for the reply... and NO... im not expecting someone to "hold my hand and baby step me through the process...", although I have never done an xml import into a SQL Server table, so I am new to this process.

    Ive read multiples of threads and websites where I have searched for how to do this, but I am not familiar enough with it to know what I could be doing wrong... thus, the reason I am posting here to get help.

    I don't know what it is, but I have read multiple posts people have made asking this similar question and there always seems to be a harsh response from someone... I gotta say, I don't really think that is the most appropriate response. Everyone has to start somewhere to learn, right?

    Anyway... I will post my xml files and errors in the next message and I hope that someone will be able to guide me in the appropriate direction.

    Thanks in advance to the kind soul that will do that...

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am just telling you how it is. We will help, bur most of us will not do it for you.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Posts
    16
    XML file:

    <?xml version="1.0"?>
    <insplist ver="1">
    <insp timestamp="1258646640" stat="G" fleet="0921" operator="Driver, Brenda Trimm" optag="66400036" repair="0" loadlocation="GPS: 39815 " starttime="1258646619" endtime="1258646653" extractor="UNK" config="WAT0664B-3.1.2" manual="0" verified="1" assetid="62" opid="39" defect="0" assetvalue="-1" cfglabel="STUDENT CHECK(v1)" loadtime="1258646675" sn="2025114" asset_exsid="" operator_exsid=""></insp>
    <insp timestamp="1258640097" stat="G" fleet="0923" operator="Driver, John Sanders" optag="66400074" repair="0" loadlocation="GPS: 39792 " starttime="1258640090" endtime="1258640108" extractor="UNK" config="WAT0664B-3.1.2" manual="0" verified="1" assetid="63" opid="75" defect="0" assetvalue="-1" cfglabel="STUDENT CHECK(v1)" loadtime="1258640125" sn="2025207" asset_exsid="" operator_exsid=""></insp>
    </insplist>

    ----------------------------------------------
    XSD file:

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="insplist">
    <xs:complexType>
    <xs:sequence>
    <xs:element minOccurs="0" maxOccurs="unbounded" name="insp">
    <xs:complexType>
    <xs:simpleContent>
    <xs:extension base="xs:unsignedShort">
    <xs:attribute name="timestamp" type="xs:string" use="optional" />
    <xs:attribute name="stat" type="xs:string" use="optional" />
    <xs:attribute name="fleet" type="xs:string" use="optional" />
    <xs:attribute name="operator" type="xs:string" use="optional" />
    <xs:attribute name="optag" type="xs:string" use="optional" />
    <xs:attribute name="repair" type="xs:string" use="optional" />
    <xs:attribute name="loadlocation" type="xs:string" use="optional" />
    <xs:attribute name="starttime" type="xs:string" use="optional" />
    <xs:attribute name="endtime" type="xs:string" use="optional" />
    <xs:attribute name="extractor" type="xs:string" use="optional" />
    <xs:attribute name="config" type="xs:string" use="optional" />
    <xs:attribute name="manual" type="xs:string" use="optional" />
    <xs:attribute name="verified" type="xs:string" use="optional" />
    <xs:attribute name="assetid" type="xs:string" use="optional" />
    <xs:attribute name="opid" type="xs:string" use="optional" />
    <xs:attribute name="defect" type="xs:string" use="optional" />
    <xs:attribute name="assetvalue" type="xs:string" use="optional" />
    <xs:attribute name="cfglabel" type="xs:string" use="optional" />
    <xs:attribute name="loadtime" type="xs:string" use="optional" />
    <xs:attribute name="sn" type="xs:string" use="optional" />
    <xs:attribute name="asset_exsid" type="xs:string" use="optional" />
    <xs:attribute name="operator_exsid" type="xs:string" use="optional" />
    </xs:extension>
    </xs:simpleContent>
    </xs:complexType>
    </xs:element>
    </xs:sequence>
    <xs:attribute name="ver" type="xs:unsignedByte" use="optional" />
    </xs:complexType>
    </xs:element>
    </xs:schema>
    ----------------------------
    SSIS package errors:

    [SQL Server Destination [988]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.

    [SQL Server Destination [988]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E57.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E57 Description: "The statement has been terminated.".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E57 Description: "String or binary data would be truncated.".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SQL Server Destination" (988) failed with error code 0xC02020C7 while processing input "SQL Server Destination Input" (1004). 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.

    [SQL Server Destination [988]] Error: A commit failed.

    [SSIS.Pipeline] Error: component "SQL Server Destination" (988) failed the post-execute phase and returned error code 0xC02092B5.

    Task Load XML failed
    ------------------------------------

    Thanks in advance... If you can help or give me a kick start to figuring this out, I would be very appreciative.

    Cavall

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your problem is in "String or binary data would be truncated.".
    This means that one of the fields contains a value that does not fit into the length that was determined by the designer. Click on Edit for the XML source in Visual Studio, click on Error Output tab on the left, and re-examine the truncation action for all string fields.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Posts
    16
    thanks for replying rdjabarov....

    I did find the field that was being truncated and was knocking my import out.
    I guess I was thinking that it shouldnt stop the process b/c I had "Truncation" set to "Ignore Error"....
    "Error" is set to "Fail component", though...

    Would it be a true statement that since "Truncation" is set to "Ignore Error" that the process should have continued without stopping?


    I have some epoch timestamp fields in the xml file and I have never worked with them before.

    Is there a way to set a format or something during the import in SSIS that will convert the epoch timestamp to a datetime field as is set for the sql server table field?

    Im aware this might be a simple question, but again, I am a beginner when it comes to SSIS, so any help would be very much appreciated.

    SQL SERVER FIELDS
    field - type - allow null
    -------------------------------
    timestamp datetime Checked
    stat nchar(1) Checked
    fleet nchar(10) Checked
    operator nvarchar(50) Checked
    optag nchar(8) Checked
    repair nchar(1) Checked
    loadlocation nchar(12) Checked
    starttime datetime Checked
    endtime datetime Checked
    extractor nchar(3) Checked
    config nvarchar(20) Checked
    manual nchar(1) Checked
    verified nchar(1) Checked
    assetid nvarchar(3) Checked
    opid nvarchar(3) Checked
    defect nchar(1) Checked
    assetvalue nvarchar(15) Checked
    cfglabel nchar(25) Checked
    loadtime datetime Checked
    sn nvarchar(10) Checked
    asset_exsid nvarchar(10) Checked
    operator_exsid nvarchar(10) Checked

    Thanks
    Cavall

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If Truncation is set to Ignore Error, then it will not raise error when truncation occurrs...As far as "epoch" timestamp values, - you'll be better off loading those into an nvarchar field, and then handling them accordingly inside SQL, rather than trying to make sense out of them inside SSIS package. (IMHO)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Posts
    16
    hmmm...

    can I atleast do something in SSIS in a derived column somehow that will take the epoch time and convert to a local datetime type format before importing into the sql server table??

    i would use this type of statement:

    DATEADD(HOUR, DATEDIFF(hour,getutcdate(),getdate()), DATEADD(s, cast(timestamp as int),'1970-01-01 00:00:00'))

    I tried to put this as the expression in a derived column, but it gave me errors about not being able to find the column "HOUR"....

    How would I do this?

    Thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •