Convert to MSSQL Change Data
Rod King asked 9 years ago
I usually develop a map as a Bulk data Load and then when it is fully developed convert it to a MSSQL Changed Data.
Rather than re-creating a copy of the map manually with a different source, is there a way to make a copy and then just change the source?
Thanks
Rather than re-creating a copy of the map manually with a different source, is there a way to make a copy and then just change the source?
Thanks
Answers
Best Answer
dusty answered 9 years ago
Rod King replied 9 years ago
dusty answered 9 years ago
It isn't possible through the interface, but through SQL you can change the datasource and is how I usually change the source. If your comfortable with SQL and want to get your hands a bit dirty, here's the script I use:
/*******************************************BEGIN SCRIPT *******************************************/
/*******************************************BEGIN SCRIPT *******************************************/
/****** Bulk data scripts ******/
SELECT QueryText, Map.MapId, DataSourceBaseId from datasourcebase
inner join Map on DataSourceBase.DataSourceBaseId = map.DataSourceId
/****** Realtime data scripts ******/
SELECT EntityDescription, EntityTechnicalName, DataSourceBase.QueryText, RealTimeDataSourceBaseId FROM [SmartConnect].[dbo].[RealTimeDataSourceBase]
inner join DataSourceBase on RealTimeDataSourceBaseId = DataSourceBaseId
/****** Change data scripts ******/
SELECT EntityTechnicalName, MapId, DataSourceBase.QueryText, [ChangeDataSourceBaseId] FROM [SmartConnect].[dbo].[ChangeDataSourceBase]
inner join DataSourceBase on [ChangeDataSourceBaseId] = DataSourceBaseId
/****** Changing the query for any map (they all store query in same table) ******/
update DataSourceBase set QueryText =
'
–insert your new query here!
'
where DataSourceBaseId = '00000000-0000-0000-0000-000000000000' –Change to mapID
/*******************************************END SCRIPT *******************************************/
With that script, you won't run the entire thing, but just highlight the section you want to run. The first section shows bulk maps, next shows realtime, third one shows change. The last part is where you paste in your new query to update the query through SQL.
The steps I go about to change a bulk map to a change data source is:
0. Make a backup of your SC database.
1. Duplicate the bulk map through the smartconnect interface
2. Go into the duplicated map and change it to change data source
3. Select the table you are putting the change datasource on but DONT open the mapping button or the query builder button because it will delete your mapping.
4. Save the map.
5. Run the Change portion of the script above and find the skeleton of what the change datasource should look like (It throws in a few things). Copy this query. Also copy the ChangeDataSourceBaseId as this is how you will update the new query.
6. Run the Bulk portion of the script above and copy the SQL code for the bulk map.
7. Combine the two queries making sure to keep the form of the default change query.
8. Copy this new query into the above script where it says "insert your new query here" and the ChangeDataSourceBaseId in place of all the zero's
9. Run the script. The change map should be finished.
10.Open the map. Open the query builder and verify the SQL was entered correctly. Open the mapping. Verify everything is mapped correctly and nothing got deleted (will only happen if it cannot find a source column it was expecting).
The process sounds a bit complicated, but after doing it once or twice, its easy to create a duplicate in less than 5 minutes. For maps with many additional columns and lookups, this is my method of choice.
Best of luck.
/*******************************************END SCRIPT *******************************************/
With that script, you won't run the entire thing, but just highlight the section you want to run. The first section shows bulk maps, next shows realtime, third one shows change. The last part is where you paste in your new query to update the query through SQL.
The steps I go about to change a bulk map to a change data source is:
0. Make a backup of your SC database.
1. Duplicate the bulk map through the smartconnect interface
2. Go into the duplicated map and change it to change data source
3. Select the table you are putting the change datasource on but DONT open the mapping button or the query builder button because it will delete your mapping.
4. Save the map.
5. Run the Change portion of the script above and find the skeleton of what the change datasource should look like (It throws in a few things). Copy this query. Also copy the ChangeDataSourceBaseId as this is how you will update the new query.
6. Run the Bulk portion of the script above and copy the SQL code for the bulk map.
7. Combine the two queries making sure to keep the form of the default change query.
8. Copy this new query into the above script where it says "insert your new query here" and the ChangeDataSourceBaseId in place of all the zero's
9. Run the script. The change map should be finished.
10.Open the map. Open the query builder and verify the SQL was entered correctly. Open the mapping. Verify everything is mapped correctly and nothing got deleted (will only happen if it cannot find a source column it was expecting).
The process sounds a bit complicated, but after doing it once or twice, its easy to create a duplicate in less than 5 minutes. For maps with many additional columns and lookups, this is my method of choice.
Best of luck.
Rod King replied 9 years ago Hi
That sounds as if it will do the job fine.
Much appreciated.
Thanks
Rod