Post reply

Importing Multiple XML files (14,500) using 'XML Source' Data Flow

  • August 22, 2009 at 1:26 am

    #209725

    Hi.

    I have checked previous posts.

    I'm trying to import many XML files - they will eventually end up in a OLE DB.

    I have been trying to 'manipulate' the XML Source Data Flow properties to pick up a number of XML files in a directory (as opposed to defining one particulaR XML file). It will allow a folder to be defined. However I got the following message from processing:

    [XML Source [2127]] Error: The component "XML Source" (2127) was unable to read the XML data. Could not find a part of the path 'C:\Documents and Settings\mcharlton\Desktop\PAS TEST BULK LOAD\'.

    Can anyone help here?

    Many thanks.

    Mat.

  • August 22, 2009 at 4:42 am

    #1043250

    So are you using a Foreach loop to process all files in a folder? If not, how are you doing it?

    Are you running it all on your local PC? Looking at that path, I'm guessing that you are ...

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 22, 2009 at 6:17 am

    #1043261

    Hi Phil

    Thanks for the reply.

    I don't currently have a Foreach loop. This sounds like and good idea (sorry I very new to this product). What do you think might be the best way to add a Foreach loop for the XML files? All my files will be in a designated folder on a d:\ or c:\.

    Yes, I'm running MS SQL Server on my work laptop The work task I have at hand involves getting 14,500 XML property survey results (one file or each house) into MS SQL Server relationally. I'll probably then use Crystal or even Reporting Services to run some reports/do BI work.

    Many thanks again for your help!

    Mat.

  • August 22, 2009 at 6:31 am

    #1043263

    Assuming that all of the XML files are of the same format, a Foreach container is the way to go.

    As an overview, you'll need to configure the Foreach container to iterate round all files in the folder you are interested in.

    Put your dataflow in the Foreach container.

    The datasource for your dataflow task will need to be set to pick up the 'current' file being iterated by the Foreach container.

    Sorry, but I don't have the time at the moment to go through the finer details. Good luck.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 25, 2009 at 3:04 am

    #1044035

    Thanks Phil.

    I'll give it a shot.

    Mat

  • August 26, 2009 at 1:13 pm

    #1045115

    Thanks again Phil and for the record to way to pick-up multiple files for importing is to use the Data Flow Task under the Control Flow tab. You place the Data Flow Task a Foreach Loop Container.

    You then define folder and all files by files type. E.g. Files = *.xls OR *.xml

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply