List of SmartConnect Maps, Source, Destination, Tasks, Calculated Fields, Mappings, Restrictions, etc.
Carmen Tuzzolino asked 6 years ago
Is there an easy and clean way to get a list of all SmartConnect Maps, Sources, Destinations, Tasks, Calculated Fields, Mappings, Restrictions, Security, Schedule, etc. configured within an environment? It is very time consuming to go through each map manually and document each component. Was hoping that there may be a report available via SmartConnect or SQL query against the SmartConnect database to produce the desired outputs.
Answers
Patrick Roth Staff answered 6 years ago
Carmen,
Unfortunately there won’t be a ‘easy and clean’ way to do this.
Out of the box, SC does not have any reporting other than what you see in the UI. (like a list of all maps for example). But even there you don’t get a report – you’d have to take a screenshot of that list.
The only “good” solution is going to be writing your own report or query and read the SC tables directly starting with SmartConnect..Map table. You’ll end up joining in a bunch of other tables depending on what exactly you need to see.
patrick
Unfortunately there won’t be a ‘easy and clean’ way to do this.
Out of the box, SC does not have any reporting other than what you see in the UI. (like a list of all maps for example). But even there you don’t get a report – you’d have to take a screenshot of that list.
The only “good” solution is going to be writing your own report or query and read the SC tables directly starting with SmartConnect..Map table. You’ll end up joining in a bunch of other tables depending on what exactly you need to see.
patrick
Old thread is old, but this might be of some help to anyone else looking for this (as I was). The tables are pretty logically named so you should be able to find them with little effort.
A couple of tools that I used were a stored proc called sp_searchit from SoundForge and a simple “find columns” script I wrote to search the sys. for keywords:
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%schedule%’
ORDER BY table_name, schema_name
I didn’t need all of the security, schedule, task info (etc), just mapping at this point, so here is the SQL I used to get what I need:
DECLARE @MapID nvarchar(255)
SET @MapID = ‘TEST_MAP’
SELECT A.MapId
, A.MapDescription
, A.CreatedDate
, A.CreatedUser
, B.[Description] Mapping
, C.Name DestinationColumn
, C.TechnicalName
, C.TypeString DataType
, CASE
WHEN ColumnID IN (SELECT CalculationColumnID FROM CalculationColumn) THEN ‘Calculation’
WHEN ColumnID IN (SELECT CustomRollingColumnID FROM CustomRollingColumn) THEN ‘Custom Rolling’
WHEN ColumnID IN (SELECT DateColumnID FROM DateColumn) THEN ‘Date Calculation’
WHEN ColumnID IN (SELECT GlobalConstantColumnID FROM GlobalConstantColumn) THEN ‘Global Constant’
WHEN ColumnID IN (SELECT GlobalRollingColumnID FROM GlobalRollingColumn) THEN ‘Global Rolling’
WHEN ColumnID IN (SELECT MsGpRollingColumnID FROM MsGpRollingColumn) THEN ‘GP Rolling’
WHEN ColumnID IN (SELECT ListColumnID FROM ListColumn) THEN ‘List Option’
WHEN ColumnID IN (SELECT LocalConstantColumnID FROM LocalConstantColumn) THEN ‘Local Constant’
WHEN ColumnID IN (SELECT MssqlLookupColumnID FROM MssqlLookupColumn) THEN ‘MSSQL Lookup’
WHEN ColumnID IN (SELECT DataSourceColumnID FROM DataSourceColumn) THEN ‘Source Column’
WHEN ColumnID IN (SELECT TranslationColumnID FROM TranslationColumn) THEN ‘Translation’
ELSE ‘Undefined’
END ColumnType
, E.Name ValueOrSourceColumn
FROM Map A
LEFT JOIN MapLine B on A.MapId = B.MapId
LEFT JOIN MapLineParameter C on B.MapLineId = C.MapLineId
LEFT JOIN ColumnSetting D on C.ColumnSettingId = D.ColumnSettingId and B.MapLineId = D.MapLineId
LEFT JOIN ColumnBase E on D.ColumnID = E.ColumnBaseId
–WHERE A.MapID = @MapID
As always, use any code at your own risk.