SSIS - import multiple xml timesheets from sharepoint to SQL table for consolidation

Question

SSIS - import multiple xml timesheets from sharepoint to SQL table for consolidation

tony.hayes

SSC Veteran

Points: 236
More actions
June 16, 2010 at 12:51 pm

#233908

I have a client who uses ms sharepoint server to store and process timesheet approvals. I need to collect daily time data from these individual XML timesheets that are all formatted the same way and, using SSIS process the data into a SQL table for consolidation. I have been trying to create an SSIS package that uses a for each loop to spool through the folder where the xml files are stored and extract the data to a MS SQL server 2005 destination (table). I used MS XSD.exe to generate an XSD file, but I cannot get column mappings to work, using the XML source and trying to select input mappings from the file only allows me to select 1 "column" of data. I know this is not completely clear, so I am including an example of the timesheet XML file in hopes that someone can point me in the right direction - for example the detail data : MonDate, MonAcct, MonReg etc...all the way thru SunTotalII needs to be extracted to a SQL table with matching column names, then the for each loop should grab the next file. HELP please
<?xml version="1.0" encoding="utf-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Timesheet-Form:-myXSD-2008-05-09T13-40-30" solutionVersion="1.0.0.478" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="https://intranet.Client-Co.com/FormServerTemplates/Timesheet%20Form.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.document.2"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:s0="http://microsoft.com/webservices/SharePointPortalServer/UserProfileService" xmlns:s1="http://microsoft.com/wsdl/types/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" xmlns:ns1="http://schemas.xmlsoap.org/wsdl/" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-05-09T13:40:30" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
<my:Items>
<my:FirstMondayItems>
<my:MonDate>2009-05-11</my:MonDate>
<my:MonAcct>PJC08207</my:MonAcct>
<my:MonReg>8</my:MonReg>
<my:MonEquip></my:MonEquip>
<my:MonLeave xsi:nil="true"></my:MonLeave>
<my:MonHoliday xsi:nil="true"></my:MonHoliday>
<my:MonSick xsi:nil="true"></my:MonSick>
<my:MonVac xsi:nil="true"></my:MonVac>
<my:MonTotal>8</my:MonTotal>
</my:FirstMondayItems>
<my:FirstTuesdayItems>
<my:TueDate>2009-05-12</my:TueDate>
<my:TueAcct>PJC08207</my:TueAcct>
<my:TueReg>8</my:TueReg>
<my:TueEquip></my:TueEquip>
<my:TueLeave xsi:nil="true"></my:TueLeave>
<my:TueHoliday xsi:nil="true"></my:TueHoliday>
<my:TueSick xsi:nil="true"></my:TueSick>
<my:TueVac xsi:nil="true"></my:TueVac>
<my:TueTotal>8</my:TueTotal>
</my:FirstTuesdayItems>
<my:FirstWednesdayItems>
<my:WedDate>2009-05-13</my:WedDate>
<my:WedAcct>PJC08207</my:WedAcct>
<my:WedReg>8</my:WedReg>
<my:WedEquip></my:WedEquip>
<my:WedLeave xsi:nil="true"></my:WedLeave>
<my:WedHoliday xsi:nil="true"></my:WedHoliday>
<my:WedSick xsi:nil="true"></my:WedSick>
<my:WedVac xsi:nil="true"></my:WedVac>
<my:WedTotal>8</my:WedTotal>
</my:FirstWednesdayItems>
<my:FirstThursdayItems>
<my:ThuDate>2009-05-14</my:ThuDate>
<my:ThuAcct>PJC08207</my:ThuAcct>
<my:ThuReg>8</my:ThuReg>
<my:ThuEquip></my:ThuEquip>
<my:ThuLeave xsi:nil="true"></my:ThuLeave>
<my:ThuHoliday xsi:nil="true"></my:ThuHoliday>
<my:ThuSick xsi:nil="true"></my:ThuSick>
<my:ThuVac xsi:nil="true"></my:ThuVac>
<my:ThuTotal>8</my:ThuTotal>
</my:FirstThursdayItems>
<my:FirstFridayItems>
<my:FriDate>2009-05-15</my:FriDate>
<my:FriAcct>PJC08207</my:FriAcct>
<my:FriReg>8</my:FriReg>
<my:FriEquip></my:FriEquip>
<my:FriLeave xsi:nil="true"></my:FriLeave>
<my:FriHoliday xsi:nil="true"></my:FriHoliday>
<my:FriSick xsi:nil="true"></my:FriSick>
<my:FriVac xsi:nil="true"></my:FriVac>
<my:FriTotal>8</my:FriTotal>
</my:FirstFridayItems>
<my:FirstSaturdayItems>
<my:SatDate>2009-05-16</my:SatDate>
<my:SatAcct></my:SatAcct>
<my:SatReg>0</my:SatReg>
<my:SatEquip></my:SatEquip>
<my:SatLeave xsi:nil="true"></my:SatLeave>
<my:SatHoliday xsi:nil="true"></my:SatHoliday>
<my:SatSick xsi:nil="true"></my:SatSick>
<my:SatVac xsi:nil="true"></my:SatVac>
<my:SatTotal>0</my:SatTotal>
</my:FirstSaturdayItems>
<my:FirstSundayItems>
<my:SunDate>2009-05-17</my:SunDate>
<my:SunAcct></my:SunAcct>
<my:SunReg>0</my:SunReg>
<my:SunEquip></my:SunEquip>
<my:SunLeave xsi:nil="true"></my:SunLeave>
<my:SunHoliday xsi:nil="true"></my:SunHoliday>
<my:SunSick xsi:nil="true"></my:SunSick>
<my:SunVac xsi:nil="true"></my:SunVac>
<my:SunTotal>0</my:SunTotal>
</my:FirstSundayItems>
<my:SecondMondayItems>
<my:MonDateII>2009-05-18</my:MonDateII>
<my:MonAcctII>PJC08207</my:MonAcctII>
<my:MonRegII>8</my:MonRegII>
<my:MonEquipII></my:MonEquipII>
<my:MonLeaveII></my:MonLeaveII>
<my:MonHolidayII></my:MonHolidayII>
<my:MonSickII></my:MonSickII>
<my:MonVacII></my:MonVacII>
<my:MonTotalII>8</my:MonTotalII>
</my:SecondMondayItems>
<my:SecondTuesdayItems>
<my:TueDateII>2009-05-19</my:TueDateII>
<my:TueAcctII>PJC08207</my:TueAcctII>
<my:TueRegII>8</my:TueRegII>
<my:TueEquipII></my:TueEquipII>
<my:TueLeaveII></my:TueLeaveII>
<my:TueHolidayII></my:TueHolidayII>
<my:TueSickII></my:TueSickII>
<my:TueVacII></my:TueVacII>
<my:TueTotalII>8</my:TueTotalII>
</my:SecondTuesdayItems>
<my:SecondWednesdayItems>
<my:WedDateII>2009-05-20</my:WedDateII>
<my:WedAcctII>PJC08207</my:WedAcctII>
<my:WedRegII>8</my:WedRegII>
<my:WedEquipII></my:WedEquipII>
<my:WedLeaveII></my:WedLeaveII>
<my:WedHolidayII></my:WedHolidayII>
<my:WedSickII></my:WedSickII>
<my:WedVacII></my:WedVacII>
<my:WedTotalII>8</my:WedTotalII>
</my:SecondWednesdayItems>
<my:SecondThursdayItems>
<my:ThuDateII>2009-05-21</my:ThuDateII>
<my:ThuAcctII>PJC08207</my:ThuAcctII>
<my:ThuRegII>8</my:ThuRegII>
<my:ThuEquipII></my:ThuEquipII>
<my:ThuLeaveII></my:ThuLeaveII>
<my:ThuHolidayII></my:ThuHolidayII>
<my:ThuSickII></my:ThuSickII>
<my:ThuVacII></my:ThuVacII>
<my:ThuTotalII>8</my:ThuTotalII>
</my:SecondThursdayItems>
<my:SecondFridayItems>
<my:FriDateII>2009-05-22</my:FriDateII>
<my:FriAcctII>PJC08207</my:FriAcctII>
<my:FriRegII>8</my:FriRegII>
<my:FriEquipII></my:FriEquipII>
<my:FriLeaveII></my:FriLeaveII>
<my:FriHolidayII></my:FriHolidayII>
<my:FriSickII></my:FriSickII>
<my:FriVacII></my:FriVacII>
<my:FriTotalII>8</my:FriTotalII>
</my:SecondFridayItems>
<my:SecondSaturdayItems>
<my:SatDateII>2009-05-23</my:SatDateII>
<my:SatAcctII></my:SatAcctII>
<my:SatRegII>0</my:SatRegII>
<my:SatEquipII></my:SatEquipII>
<my:SatLeaveII></my:SatLeaveII>
<my:SatHolidayII></my:SatHolidayII>
<my:SatSickII></my:SatSickII>
<my:SatVacII></my:SatVacII>
<my:SatTotalII>0</my:SatTotalII>
</my:SecondSaturdayItems>
<my:SecondSundayItems>
<my:SunDateII>2009-05-24</my:SunDateII>
<my:SunAcctII></my:SunAcctII>
<my:SunRegII>0</my:SunRegII>
<my:SunEquipII></my:SunEquipII>
<my:SunLeaveII></my:SunLeaveII>
<my:SunHolidayII></my:SunHolidayII>
<my:SunSickII></my:SunSickII>
<my:SunVacII></my:SunVacII>
<my:SunTotalII>0</my:SunTotalII>
</my:SecondSundayItems>
</my:Items>
<my:Employee>xxxx xxxxxx</my:Employee>
<my:WeekEnding>2009-05-24</my:WeekEnding>
<my:TotalReg>80</my:TotalReg>
<my:TotalLeave>0</my:TotalLeave>
<my:TotalHoliday>0</my:TotalHoliday>
<my:TotalSick>0</my:TotalSick>
<my:TotalVac>0</my:TotalVac>
<my:Total>80</my:Total>
<my:FormApprovalStatus>Approved by Client-Co\xxxx</my:FormApprovalStatus>
<my:FormApproverComments></my:FormApproverComments>
<my:Manager>Client-Co\xxxx</my:Manager>
<my:CurrentUser>Client-Co\xxxx</my:CurrentUser>
<my:IsSubmitEnabled>No</my:IsSubmitEnabled>
<my:IsManager>Yes</my:IsManager>
<my:IsAREnabled>Yes</my:IsAREnabled>
<my:IsCommentsEnabled>Yes</my:IsCommentsEnabled>
<my:EmployeeAccountName>Client-Co\xxxxxxx</my:EmployeeAccountName>
<my:JobFilter>3</my:JobFilter>
<my:IsCopyEnabled>No</my:IsCopyEnabled>
<my:PreparedFor>xxxx xxxxxx</my:PreparedFor>
<my:TotalRegI>40</my:TotalRegI>
<my:TotalLeaveI>0</my:TotalLeaveI>
<my:TotalHolidayI>0</my:TotalHolidayI>
<my:TotalSickI>0</my:TotalSickI>
<my:TotalVacI>0</my:TotalVacI>
<my:TotalI>40</my:TotalI>
<my:TotalRegII>40</my:TotalRegII>
<my:field9></my:field9>
<my:TotalLeaveII>0</my:TotalLeaveII>
<my:TotalHolidayII>0</my:TotalHolidayII>
<my:TotalSickII>0</my:TotalSickII>
<my:TotalVacII>0</my:TotalVacII>
<my:TotalII>40</my:TotalII>
<my:IsSaveEnabled>No</my:IsSaveEnabled>
</my:myFields>

Viewing 2 posts - 1 through 1 (of 1 total)

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

tony.hayes SSC Veteran Points: 236 More actions · Answer 1

OK, so I was able to build XML schemas that validate the data in the timesheet files, but despite the fact that each of the lines of data is validated I receive the following at the end of data flow task package execution :

"[DTS.Pipeline] Information: "component "SQLDestMon1" (93952)" wrote 0 rows. "

"[DTS.Pipeline] Information: "component "SQLDestTues1" (84712)" wrote 0 rows. "

"[DTS.Pipeline] Information: "component "SQLDestWeds1" (86914)" wrote 0 rows. "

etc...

Of course the component name and ID change from data element to data element, but the result is a package that runs with 0 errors, but doesn't load data to the sql database. If anyone has experience with loading xml files to SQL via SSIS, please let me know what you would need in the way of code to help with this. Thanks