Creating SSIS package for XML file for required data in Data

Developers, I am working on SSIS and I want to create a package to populate the XMLdata in the database from an XML source file and my source file sample data is here:

In the database table I have columns TimeStamp column and the Meterdirection column and I need the both values to populate in the columns of the table and there is one more table in available in the database with Meterdirection type with Delivered=1 and Received=2, So now how can I create the package in SSIS main tricky task is here I am not getting the MeterDirection column values, can anyone help me?

Your XML file needs to be structured properly. For example:


    
        2023-10-01T12:00:00
        Delivered
    
    
        2023-10-01T12:15:00
        Received

Verify that your database tables are set up as follows:

   CREATE TABLE MeterData (
       TimeStamp DATETIME,
       MeterDirection INT
   );

   CREATE TABLE MeterDirectionType (
       MeterDirection VARCHAR(50),
       DirectionCode INT
   );

Populate the mapping table:

INSERT INTO MeterDirectionType (MeterDirection, DirectionCode)
   VALUES ('Delivered', 1), ('Received', 2);

Open SSDT and create a new SSIS project.
Add a Data Flow Task to the control flow.

Add an XML Source :
- Drag and drop an XML Source component into the Data Flow.
- Configure the XML Source:
- Set the XML file location to your XML file.
- Use the XSD file or generate one if needed to define the schema.
- Map the TimeStamp and MeterDirection fields.

Add a Lookup Transformation:
- Drag and drop a Lookup Transformation component.
- Configure the Lookup:
- Set the connection to your database.
- Use the MeterDirectionType table as the reference.
- Map the MeterDirection from the XML source to the MeterDirection column in the lookup table.
- Add the DirectionCode to the output columns.

Add an OLE DB Destination :
- Drag and drop an OLE DB Destination component.
- Configure the OLE DB Destination:
- Set the connection to your database.
- Map the TimeStamp from the XML source and the DirectionCode from the Lookup Transformation to the MeterData table.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi1y ago
Your XML file needs to be structured properly. For example:
```
    
        2023-10-01T12:00:00
        Delivered
    
    
        2023-10-01T12:15:00
        Received
    
```
Verify that your database tables are set up as follows:
```
   CREATE TABLE MeterData (
       TimeStamp DATETIME,
       MeterDirection INT
   );

   CREATE TABLE MeterDirectionType (
       MeterDirection VARCHAR(50),
       DirectionCode INT
   );
```
Populate the mapping table:
```
INSERT INTO MeterDirectionType (MeterDirection, DirectionCode)
   VALUES ('Delivered', 1), ('Received', 2);
```
Open SSDT and create a new SSIS project.
Add a Data Flow Task to the control flow.

Add an XML Source :
- Drag and drop an XML Source component into the Data Flow.
- Configure the XML Source:
- Set the XML file location to your XML file.
- Use the XSD file or generate one if needed to define the schema.
- Map the TimeStamp and MeterDirection fields.

Add a Lookup Transformation:
- Drag and drop a Lookup Transformation component.
- Configure the Lookup:
- Set the connection to your database.
- Use the MeterDirectionType table as the reference.
- Map the MeterDirection from the XML source to the MeterDirection column in the lookup table.
- Add the DirectionCode to the output columns.

Add an OLE DB Destination :
- Drag and drop an OLE DB Destination component.
- Configure the OLE DB Destination:
- Set the connection to your database.
- Map the TimeStamp from the XML source and the DirectionCode from the Lookup Transformation to the MeterData table.