SSIS How to UPDATE instead of INSERT (UPSERT)

I'm working on several SSIS Transfer Packages right now and I just figured out how to do an "UPSERT": How to insert rows that don't exist in the destination table and how to update rows that exist in both source and destination tables. This will work across servers (which is the environment I'm working on).

This is how one table/view would look like in an SSIS package:

Yes, that means (as far as I know) that you have to create four objects for every table you want to transfer data from/to.

The Source object is, of course, connected to the Source database's table/view. The other objects beneath it (the Lookup, Destination and Update objects) are all connected to the Destination database's table/view.

The Update object is the only tricky element in this package. I specify the update script manually, which looks like:

UPDATE dbo.MyTable

SET Column1 = ?

,Column2 = ?

,Column3 = ?

WHERE ID= ?

(see: Generate SSIS Update script which I use to generate the update script for each table that I have in my SSIS package)

Each "?" will be automatically mapped by the system based on the order you defined it. All you have to do next is to specify the Column Mappings. Make sure that you do it in the right order like the image below:

Note how the ID is specified last -- that's because its corresponding "?" was specified last in the Update statement.

How to select value from strongly typed XML

Table Name: TableHasXML

has Column: XML_Column xml(XMLCollectionSchema)

XML data is similar to:

[root xmlns="http://thingswelovetohate.com/schema/2010/PinayProgrammer.xsd" ....]

[row ]

[column1]Things We Love to Hate[/column1]

[column2]http://www.thingswelovetohate.com[/column2]

[/row]

[row]

[column1]Pinay Programmer[/column1]

[column2]http://beingoyen.blogspot.com[/column2]

[/row]

....

[/root]

(yes, I know that was sloppy but blogger wouldn't let me just copy-paste my actual schema. Blogger treats any greater than/less than signs as HTML tags)

XML_Column.query('declare default element namespace "http://thingswelovetohate.com/schema/2010/PinayProgrammer.xsd";

data(/root/row[Column1="Things We Love to Hate"]/Column2)')

FROM TableHasXML

Result = http://www.thingswelovetohate.com

If your XML column or variable doesn't have a namespace then leave out the namespace declaration as well:

XML_Column.query('data(/root/row[Column1="Things We Love to Hate"]/Column2)') FROM TableHasXML

How to know if a string is part of an XML?

I have a table named "TempTable" with an XML column named "XML_Collection". Now I want to know if the string "My Valentine" appears in any of those XML rows.

I used the string manipulator: PATINDEX

SELECT * from TempTable WHERE PATINDEX('%My Valentine%', convert(varchar(max),XML_Collection)) > 0

Important notes:

• be sure to convert the XML type to varchar(max) first before using any string manipulator functions
• if PatIndex == 0 then that string pattern does not exist
• if PatIndex > 0 then that string pattern exists

TSQL Latin Collation Error

Error encountered:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I'm not sure if it's as convenient with the earlier versions but SQL Server 2008 allows you to easily change the collation of your database through the Properties window.

The first thing you would have to do is to drop all connections to the database. This can be easily done by right clicking on you database --> Tasks --> "Detach..." then tick the "Drop all connections" check box.

Then simply restore the database using your back up file (it's usually the same name as your database with a .bak file extension).

Now you're sure no one is initially connected to the database.

Right click on the database --> Properties --> Options then on the top is a drop down for the Collation options. We generally choose "SQL_Latin1_General_CP1_CI_AS".

Select XML Schema Collection type of an XML Column

I spent an afternoon looking for a way to get the XML Schema Collection type of a given column.

I wanted to copy the contents of a table (that has an XML column with a user defines Schema Collection) to another table but I get this error message: Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query. 

I understand that I have to CONVERT it to the same XML Schema Collection but how do I do so dynamically?

I ended up resorting to MSDN's SQL forums. The next morning, Peso, an MVP, gives me the answer:

SELECT object_name(ac.object_id) AS TableName,
ac.name as ColumnName,
  x.name AS CollectionName
FROM sys.all_columns AS ac
LEFT JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = ac.xml_collection_id
WHERE object_name(ac.object_id) = 'MyTableNameHere'