Search code examples
sql-serverssisetlsql-server-data-tools

Insert a data in OLE DB destination

I have created a SSIS package. It'll load a XML file and store the data in the database.

enter image description here

I need to use a value (FILE_NAME) from FILE_INFO which is passed from the XML file into the OLE DB Destination in SQL Command mode. How can I use the FILE_NAME in the sql query.

This is my SQL query inside OLE DB Destination

Insert into DummyFile(DummyFileName, DummyFileStatusID)
VALUES ('I NEED TO INSERT THE FILE NAME HERE',
       (Select FileStatusID from DummyFileStatus where StatusName='Created'));

Please advice.

Solution

  • I think you are looking for a Derived Column

    If you want to insert the values into another OLEDB Destination DummyFileStatus, you can add a MutliCast transformation that allow you to insert data into multiple OLEDB Destination.

    Or Just add another DataFlow Task that will be executed after this DataFlow task, to import Data from DummyFile to DummyFileStatus and use a Derived Column within it.

    Additional Informations