SSIS 2005 Import Export XML

Please see the attached doc for a correct reading. We require a small SSIS 2005 project on 2 different steps: 1. Import from a XML file to a SQL Server into a table R5READINGS 3 columns, a. From the key Einheit we will get the DatenReferenz on the RefValue b. From the key Ablesedatum we will get the Datum Value on the RefValue c. From the key Wert we will get the value in it i. Import Example 1. The only fields that we require and will work with, are the ones with value with the “km” as a value, all others will be disregarded 2. Value used: DatenReferenz RefValue -> Object ID (ex: 378008200016) 3. On this field is 12 character long (12 plus the -), and we must and the “-” character before the last digit (this digit goes always from 0 to 9) 4. Value used: Ablesedatum Datum Value-> Date (ex: 29.04.2010 14:10:05) 5. Value used: Wert -> Value(Ex: 3.141) a. The value expected on the xml is always greater than the current value on the database, we will not check if it is not correct. ii. We will import to the table R5READINGS the preceding values and WILL add to the column SOURCESYSTEM the value CIDIS to indicate it was an automatic insert, not manual. 2. Export a file (XML or CSV) from the table R5READINGS – Only hand introduced values on the application will be exported also only the, this will be monitored by the column REA_SOURCESYSTEM, if it has value ‘CIDIS’ a. We will create a View called METER_READINGS and export all the records from the table R5READINGS, according to the given structure if on the column REA_SOURCESYSTEM is not filled with the value CIDIS b. The columns exported from the table will be the following: i. REA_READING (ex: 3.141) ii. REA_OBJECT(ex: 378008200016) iii. REA_DATE(ex: 29.04.2010 14:10:05) iv. REA_SOURCESYSTEM (ex:CIDIS) 3. Tracking: a. At the end on the export we are going to update the table unesoft_interface with the last exported value from the table R5READINGS of the columns REA_SQLIDENTITY. 3.141 ZaehlerAblesungen -> Meter Reading Zaeler -> Meter Einheit -> Unit Eigentuemer -> Owner -> No need DatenReferenz -> Owner Bezeichnung -> Descripcion Zaehlerart -> Measure type Ablesedatum -> Reading date Wert -> Value Insert example: INSERT INTO R5READINGS ([REA_METER] ,[REA_DATE] ,[REA_READING] ,[REA_OBTYPE] ,[REA_OBRTYPE] ,[REA_OBJECT] ,[REA_UOM] ,[REA_CALCUOM] ,[REA_RECORDED] ,[REA_SOURCESYSTEM] ,[REA_SOURCECODE] ,[REA_TRANSORGID] ,[REA_TRANSCODE] ,[REA_TRANSGROUP] ,[REA_OBJECT_ORG]) VALUES (NULL, '29.04.2010 14:10:05', 10000, 'RAIL', 'A', '00000000011-7', 'km', 'km', null, 'CIDIS', null, null, null, null, '*')