Import multiple xml files(sharepoint webservices out put) to sql database using ssis
Hi, I am importing xml file in to sql server database i was able to do this task for single xml file in to sql database using ssis( i am using dataflow task, xml source, data conversion, oledb destination creating a ssis package). Now i am having 3 xml files, need to store in different tables in sql server database can someone explain how i can import multiple xml files in to different tables in sql.can we create a xsd for each xml file while importing.
November 15th, 2010 2:03am

does the structure of data in 3 xml file is same? if yes, you can use a foreach loop which loops in the directory which contains 3 xml files and then put your data flow task in the foreach loop container and set xml file from variable in the xml source But if structure of data is different you should create 3 different source/mapping/destinationhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 2:07am

Hi, Thanks for your quick reply, the structure of the three xml files is same, i have tried with foreach loop but didnt understand how to use it. can you please explain briefly how to use. here i will explain my task clearly. I have 3 xml files (file1.xml,file2.xml,file3.xml which are having the same structure ) need to import in to sql database ( three tables are created table1, table2, table3 in sql database) can you explain how i can do this task. thanks in advance
November 15th, 2010 2:20am

first of all you need a mapping structure between XML file names and sql server tables. for example you can create a Mapping table in sql server, and fill it with these values: SourceXMLFile DestinationTableName --------------------------------------------------------- file1.xml table1 file2.xml table2 file3.xml table3 and then solution: Create there variables in package scope: Variable name data type scope default value --------------------------------------------------------------------------- MappingObject Object package SourceXmlFile String package file1.xml DestinationTableName String package table1 SourceXMLDirectoryPath String package D:\XMLFiles\ SourceXMLFullPath String package then right click on the SourceXMLFullPath variable, select properties, set expression as: @[User::SourceXMLDirectoryPath]+@[User::SourceXmlFile] and set EvaluateAsExpression property to true then create a data flow task for fetching information from MappingTable in the data flow task use an OLEDB Source pointing to the MappingTable, add a record set destination, set variable name as MappingObject, map columns then go back to control flow add a foreach loop container, set enumerator as ADO enumerator set the MappingObject there, then go to variable mapping tab set these mappings: variable name index ------------------------------------- User::SourceXmlFile 0 User::DestinationTableName 1 then add your data flow task which is work with single xml file to sql server table inside the foreach loop container then in xml source editor, set "data access mode" as "xml file from variable", and set "variable name" as : User::SourceXMLFullPath then double click on the oledb destination and set "data access mode" as "table name or view name from variable" and set "variable name" as User::DestinationTableName That's all.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 2:45am

Hi Reza, do i need to create a mapping table in sql server management studio. can we do the task with out creating mapping tables. can we use FOREACH FILE ENUMERATOR directly and load the 3 xml files in to xml source and then into database
November 15th, 2010 5:06am

you need it because you want to import each xml file in different table you need a way to understand destination table name from xml file name. and mapping table is a way to do it. if your tablenames be exactly same as your xml file names, then you don't need mapping table.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 5:12am

Hi Reza, xml file names and table names are same. i can say that i have users.xml file and users.dbo table in sql in the same way i have 2 other xml files and tables. from where can i proceed in this situation. thank you so much for replying very quickly
November 15th, 2010 5:19am

OK, so go with this way now: Create there variables in package scope: Variable name data type scope default value --------------------------------------------------------------------------- MappingObject Object package DestinationTableName String package SourceXMLFullPath String package d:\testfolder\file1.xml ( Note that this default value should be a valid path ) then right click on the DestinationTableName variable, select properties, set expression as: REPLACE(@[User::SourceXMLFullPath],".xml","") and set EvaluateAsExpression property to true add a foreach loop container, set enumerator as file enumerator, set source directory then go to variable mapping tab set these mappings: variable name index ------------------------------------- User::SourceXMLFullPath 0 then add your data flow task which is work with single xml file to sql server table inside the foreach loop container then in xml source editor, set "data access mode" as "xml file from variable", and set "variable name" as : User::SourceXMLFullPath then double click on the oledb destination and set "data access mode" as "table name or view name from variable" and set "variable name" as User::DestinationTableName That's all.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 6:18am

Hi Reza, i have one more doubt, you said "then add your data flow task which is work with single xml file to sql server table inside the foreach loop container" do i need to place the .dtsx package here or place the data flow task follow the same procedure for one xml file i followed this procedure 1. add a dataflow task in control flow double click on it 2. add the xml source task given the physical path of xml file 3. add the data conversion task convert the data format 4. finally add the oledb destination task give the connections and mappings, execute the package.
November 15th, 2010 6:49am

Hi Reza, i have one more doubt, you said "then add your data flow task which is work with single xml file to sql server table inside the foreach loop container" do i need to place the .dtsx package here or place the data flow task follow the same procedure for one xml file i followed this procedure 1. add a dataflow task in control flow double click on it 2. add the xml source task given the physical path of xml file 3. add the data conversion task convert the data format 4. finally add the oledb destination task give the connections and mappings, execute the package.
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 6:49am

Hi Reza, i have one more doubt, you said "then add your data flow task which is work with single xml file to sql server table inside the foreach loop container" do i need to place the .dtsx package here or place the data flow task follow the same procedure for one xml file i followed this procedure 1. add a dataflow task in control flow double click on it 2. add the xml source task given the physical path of xml file 3. add the data conversion task convert the data format 4. finally add the oledb destination task give the connections and mappings, execute the package.
November 15th, 2010 6:49am

place the data flow task with same procedure in the Foreach loop container, and then set variable names in the source and destination as I suggested in previous post.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 6:53am

place the data flow task with same procedure in the Foreach loop container, and then set variable names in the source and destination as I suggested in previous post.http://www.rad.pasfu.com
November 15th, 2010 6:53am

place the data flow task with same procedure in the Foreach loop container, and then set variable names in the source and destination as I suggested in previous post.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2010 6:53am

Hi Reza, i followed the above steps but i am getting the following error when i click on mappings tab in oledb destination. Error at Data Flow Task [OLE DB Destination [133]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [OLE DB Destination [133]]: Opening a rowset for "G:\Documents and Settings\folder1\test3methods\User_Groups" failed. Check that the object exists in the database. i searched in the google but didnt find any. my database tables and xml files are in different location does it make any difference. do i need to write c# scripts to work this out. thanks in advance.
November 16th, 2010 2:04am

Hi Reza, i followed the above steps but i am getting the following error when i click on mappings tab in oledb destination. Error at Data Flow Task [OLE DB Destination [133]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [OLE DB Destination [133]]: Opening a rowset for "G:\Documents and Settings\folder1\test3methods\User_Groups" failed. Check that the object exists in the database. i searched in the google but didnt find any. my database tables and xml files are in different location does it make any difference. do i need to write c# scripts to work this out. thanks in advance.
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2010 2:04am

Hi Reza, I am trying to follow by using the mapping tables, can i know how to create mapping tables in sql management studio.
November 16th, 2010 5:00am

Hi Reza, I am trying to follow by using the mapping tables, can i know how to create mapping tables in sql management studio. create a simple table, open SSMS, under database , under tables, right click create new table, set two columns with names: SourceXMLFile DestinationTableName and fill data as I suggested in first post.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2010 9:59am

Hi Reza, i am following the mapping table procedure can you please check my comments in bold first of all you need a mapping structure between XML file names and sql server tables. for example you can create a Mapping table in sql server, and fill it with these values: SourceXMLFile DestinationTableName --------------------------------------------------------- file1.xml table1 file2.xml table2 file3.xml table3 and then solution: Create there variables in package scope: Variable name data type scope default value --------------------------------------------------------------------------- MappingObject Object package SourceXmlFile String package file1.xml DestinationTableName String package table1 SourceXMLDirectoryPath String package D:\XMLFiles\ SourceXMLFullPath String package then right click on the SourceXMLFullPath variable, select properties, set expression as: @[User::SourceXMLDirectoryPath]+@[User::SourceXmlFile] and set EvaluateAsExpression property to true then create a data flow task for fetching information from MappingTable in the data flow task use an OLEDB Source pointing to the MappingTable, add a record set destination, set variable name as MappingObject, map columns then go back to control flow add a foreach loop container, set enumerator as ADO enumerator set the MappingObject there, then go to variable mapping tab set these mappings: variable name index ------------------------------------- User::SourceXmlFile 0 User::DestinationTableName 1 then add your data flow task which is work with single xml file to sql server table inside the foreach loop container then in xml source editor, set "data access mode" as "xml file from variable", and set "variable name" as : User::SourceXMLFullPath then double click on the oledb destination and set "data access mode" as "table name or view name from variable" and set "variable name" as User::DestinationTableName i am following the above procedure but i didnt understand the step "then create a data flow task for fetching information from MappingTable in the data flow task use an OLEDB Source pointing to the MappingTable,( in oledb source editor, in data acces mode do i need to select "table or view" or "table name or view variable name" what is "add a record set destination" can you please explain this steps i am really stuck up at this point. add a record set destination, set variable name as MappingObject, map columns then go back to control flow" can you please explain the oledb source steps which data access mode i have to select thank you so much for your help
November 17th, 2010 6:33am

Hi Reza, i am able to get the data from file1.xml in to the "recordset destination" and i am getting the error at foreach loop. here is the error "Error: Variable "User::Mappingobject" does not contain a valid data object" can i know where i am doing the mistake.
Free Windows Admin Tool Kit Click here and download it now
November 17th, 2010 8:43am

when you create mapping table, and fill it with values , in the data flow , in the oledb source, set access mode as "table name or view name" and in table name , select mappingTable from the list in the recordSet destination you can map columns too. if this doesn't helped you yet, give me an screenshot of your schema and configurationshttp://www.rad.pasfu.com
November 17th, 2010 11:26am

Hi Reza, I have followed the above steps but still getting the same error "Error: Variable "User::Mappingobject" does not contain a valid data object". the process i followed. i created the variables, in the first data flow task added oledb source and recordset destination, made the configurations, then added foreach loop and added the data flow task in it i added the regular procedure for 1 xml file. i am forwarding the screen shots here. can i have your mail id please so that i can forward all the screen shots thanks,
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2010 2:44pm

send me at : a dot raad dot g at gmail dot comhttp://www.rad.pasfu.com
November 18th, 2010 2:47pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics