Howto Change SQL Adapter Schema without loosing precious map connections

1)      Open
the Solution Explorer

2)      Save
a copy of the current Schema just in case

a.       Right
Click the Schema that you want to change

b.      Select
“Open With …”

c.       In
the “Open With…” dialog , select “Xml editor” and click Ok.

d.      Select
all the text from the XML editor, Copy and Past it into Notepad (or any other
editor of your choosing)

3)      Copy
and past the following information to another Notepad window as it will be
needed later on

a.       Target
Namespace (usually http://mynamespace)

b.      Document
Receive root

c.       The
SQL Select Command with the XMLauto

4)      You
can now modify your database if you haven’t already done so

5)      In
the solution explorer, re-run the “Add Generated Item” wizard with the Notepad-saved
information and you modified query

a.       Right-click
on the project select Add/Add Generated Item

b.      In
the “Add Generated Item Window” Select “Add Adapter Metadata” and click Add

c.       In
the “Add Adapter Wizard” Window Select SQL and click Next

d.      Set
the connection-string info then click Ok  (PS Note that This never worked for me with
Mixed Mode, on works with windows authentication)

e.       In
the Transport Schema Windows Past your Notepad-Saved information

f.       
In the SQL Query window Paste and edit your command as
see fit ( it’s a good idea to try your queries first)

g.       Click
finish to end the Wizard

6)      Your
new SQL Adapter schema should now be created. Open it with the XMLeditor

7)      Copy
the whole text and past in you old schema file still opened in XMLeditor mode.

8)      Save
and verify that you map still has the correct link and transformation information.

9)      You
can get rid of the extra orchestration created.

10)  You can now
delete the new schema as the old on is now updated.