August 24, 2009 at 4:39 am
I've got one simple question with no easy answer...
How do you export a table with a HierarchyId column to another SQL 2008 database through SSIS?
Bascially I've been developing a db with a table that contains a HierarchyId column. I've been populating it, but to give the app developer a head start I've just given him the ddl to add to his own db. Now I want to push my data over but can't for the life of me work out how! I've spent the last hour plus trying to find answers but can't.
It isn't supported if you use the SQL 10 Native Client, the .Net Framework Data Provider for SQLServer also doesn't support it... I am at a loss...
Thanks
Kevin
August 24, 2009 at 5:03 am
Ok I've answered this one myself, but there could be better solutions.
I created a view on my table with the hierarchyId column as a string. i.e:
CREATE VIEW vwHier
AS
SELECT [Node] = Node.ToString()
,...
,...
FROM tblHier
Then exported that view to my developer's box.
I've then inserted into his table tblHier as follows
INSERT INTO tblHier
(
Node
,...
,...
)
SELECT [Node] = hierarchyid::Parse(Node)
,...
,...
FROM tblHierTemp
...Well bit long winded but at least it works...
If you've got a better idea please let me know! I am sure it won't be the last time I need to copy hierarchy data over!
N.B His table contained no data, things may have been more interesting if I had to do an update or insert. But then that should fall down to the keys and constraints I have on the table...
Kev
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply