Every node must be sorted, and this XML has four such nodes - person, name, addresses, and address.Īfter all data has been sorted, we are ready to create joins. It will also create appropriate IDs so nodes can be linked. This will scan your XML and read every node. Point your file connection to the correct place on your drive.
Now that we have properly prepared our tables, we are ready to read our file and insert new rows. This time, there are no parameters or results. commit transaction end Ĭall this procedure with an Execute SQL task. Create a procedure that will wipe away all existing data.Ĭreate procedure. We want to prepare our tables for incoming data. We will use it later when we insert our XML data into tables. Here, the BatchId is the first value returned (0-indexed). We set the Result Set to “Single Row” on the general page. Create a user variable called BatchId, and save the result to that variable. Next, set the input parameters for the stored procedure - StartTime and Filename.įinally, we need to capture the result set. My SQL task will use this existing connection. I have already added an ADO.NET connection to my database. Add a new Execute SQL task to your workflow. Our first SSIS step is to create a new record in this table and save the ID. add int not null - Add BatchId column to Address. Batch where BatchId = scope_identity () commit transaction end - Add BatchId column to Person.
Batch ( StartTime, Filename ) values ( startTime, filename ) select top ( 1 ) BatchId, StartTime, Filename from dbo. startTime datetimeoffset = null, filename varchar ( 200 ) = null as begin begin transaction insert into dbo. ( int not null identity ( 1, 1 ), datetimeoffset null, varchar ( 200 ) null, constraint primary key clustered ( BatchId ) ) - Create a new stored procedure for creating a new batch entry.