CODEWITHSUNDEEP
sql-servermergessisetlsql-server-data-tools
Ranjith Varatharajan
Ranjith Varatharajan

Reputation: 1694

How to get and store data from different source in SSIS package

I have a XML Source file from which i have to get the "File Name" and a table from which i have to get the file status. Once i get both the values, I need to save the two data in a table.

XML:

<File>
<File_Info>
    <File_Name>Test1</File_Name>
    <File_Path>BLABLABLA</File_Path>
    <File_Ext>.xml</File_Ext>
</File_Info>
</File>

Table:

FileStatusID  -  Status
1             -  Created
2             -  Processed

There are no relationships between these two sources.

How can I store File_Name from XML source and FileStatusID from table to a table?

File Table:

FileID - FileName - FileStatusID
1      - Test     - 1
2      - Test2    - 1

This is my Package

enter image description here

Upvotes: 1

Views: 285

Answers (1)

Hadi
Hadi

Reputation: 37313

If you are looking to merge both data sources based on the order: First row of XML with first Row of OLEDB, you can just add a script component transformation after each data source.

On each script component transformation, add an Output Column of type DT_I4 (integer) (let's assume that it's name is AutoNumCol)

Write the following script to generate Autonumber, (i used VB.Net):

Public Class ScriptMain
    Inherits UserComponent

    Private intID as integer = 0

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        intID += 1

        Row.AutoNumCol = intID
    End Sub

End Class

And use these two column in the Merge Join

*Note make sure that you have marked the script output as IsSorted, and changed the AutoNumCol SortKeyPosition to 1:

enter image description here

enter image description here

Upvotes: 2

Related Questions