November 20, 2010 at 2:46 pm
Hi,
I am using SSIS 2005.
My requirement is to update a set of records in a database table(SQL Server 2000). The records to be updated are based on a set of XML nodes.
As an example, The table is as below:
contract_key hotel_name
296626 HOLIDAY INN
308815 DAYS INN LEDGEWOOD
345292 SHERATON MAHWAH HOTEL
366670 HILTON SHORT HILL
383738 COURTYARD MAHWAH
456754 COMFORT INN MAHWAH
784229 SHERATON PARSIPPANY
script:
CREATE TABLE dbo.HTL_CONTRACTS(
contract_key INT,
hotel_name VARCHAR(100)
)
GO
INSERT INTO HTL_CONTRACTS
SELECT 456754, 'COMFORT INN MAHWAH' UNION
SELECT 308815, 'DAYS INN LEDGEWOOD' UNION
SELECT 784229, 'SHERATON PARSIPPANY' UNION
SELECT 345292, 'SHERATON MAHWAH HOTEL' UNION
SELECT 296626, 'HOLIDAY INN' UNION
SELECT 383738, 'COURTYARD MAHWAH' UNION
SELECT 366670, 'HILTON SHORT HILL'
GO
The XML is as below:
<?xml version="1.0" encoding="utf-8"?>
<File>
<Header>
<row PRDSId="XXX" ProgramNumber="2130"/>
</Header>
<Data>
<row ContractKey="308815" HotelName="DAYS INN LEDGEWOOD"/>
<row ContractKey="296626" HotelName="HOLIDAY INN Changed Name"/>
<row ContractKey="366670" HotelName="HILTON SHORT HILL" />
</Data>
</File>
My requirement is to update all the hotels in the XML file to DB table.
Approach 1: Find all Contract Keys in the XML and delete the corresponding entries in the database. Then insert all the data from the XML to DB table.
Approach2: Find all the contract keys in the XML which exist in the DB table and UPDATE them. Then INSRET the ones which do not exist in the DB table.
Constraints:
1. I don't want to use the individual delete queries because of the performance issue.
2. I don't want to send the comma seperated set of contract keys to the database because of the size restrictions of string datatypes.
3. I can not create/ change any tables
I can read the XML file in SSIS to get the list of all contract keys in a SSIS in-memory recordset.
Is there any efficient way to update a set of records from the Database?
November 20, 2010 at 4:27 pm
SQL Server 2005 does not come with ADO.NET connection so you need to create XSD to load the XML into a XML data flow task then create execute SQL Task to pass the data into your table.
In 2008 it is ADO.NET connection and call the DataSet.ReadXML method in ADO.NET classes. Then consume either a stored procedure or .sql file in the ADO.NET command object.
Kind regards,
Gift Peddie
November 20, 2010 at 11:54 pm
Thanks....
I have done the first part i.e, using a data flow task and getting the list of values (a set of Contract Keys in my example).
I am not quite sure how I can take this data to database without creating any new table. For now, I have used a ADO.NET recordset as the destination.
Can you please explain?
November 21, 2010 at 10:16 am
SMNayak (11/20/2010)
Thanks....I have done the first part i.e, using a data flow task and getting the list of values (a set of Contract Keys in my example).
I am not quite sure how I can take this data to database without creating any new table. For now, I have used a ADO.NET recordset as the destination.
Can you please explain?
I did not say use ADO.NET in 2005 because of known issues but I know if you use XSD in XML data flow task you can use containers and call execute sql task to pass the data to your table. You don't need SSIS for that just connect to the server with the destination database and make sure you either have a table or need to create one during or before you execute the package.
http://technet.microsoft.com/en-us/library/ms141676.aspx
Another very simple solution is to write CLR stored proc using ADO.NET to insert the data in SQL Server 2005 then create a package with single execute SQL task that takes the data from table SQL Server 2005 to 2000 table.
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply