Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jo deRuiter]

Hey,

One of my clients has hundreds of maps running and needed a full listing of each one, where the source is, the exact destination and other map info.

The SmartConnect Tables are thorough, but the source and destination info are in separate tables so if yours is missing here, look at the tables and join them up as in my query below – for instance, each Excel version source has its own table in the database.  This is a hair aggravating, but all the info is there and it is pretty good.

Here you go!!!

ALTER VIEW [dbo].[ADBS_MAP_LISTING]
AS
SELECT IG.IntegrationGroupName
,[MapId]
,[MapDescription] as 'Description of Map'
,CASE WHEN [DestinationType] LIKE '%dYNAMICS.GP%' THEN 'GP '+d.[GroupName] when [DestinationType] like '%mssql%' then gp.[DefaultMssqlConnection] when [DestinationType] like '%generic%' then 'Test File' end as 'Integrating To Destination'
,CASE WHEN [DestinationType] LIKE '%dYNAMICS.GP%' THEN d.[NodeTypeName] when [DestinationType] like '%mssql%' THEN GP.[MssqlTable] END as 'Exactly Where in Desitnation'
,'' as 'Resulting Batch Name'
,convert(varchar,[LastRunDate],101) as 'Last Run on Date'
,[RunNumber] as 'Latest Run Number'
,CASE WHEN [DataSourceType] LIKE '%Folder%' then 'Folder Data Source' when [DataSourceType] like '%Multi%' then 'Multi Data Source' when [DataSourceType] like
'%Text%' then 'TXT or CSV Direct File' when [DataSourceType] like '%odbc%' then 'SQL or ODBC Datasource'
when [DataSourceType] like '%Excel2007%' then 'Excel 07 Direct File' when [DataSourceType] like '%Excel2013%' then
'Excel 13 Direct File' when [DataSourceType] like '%GP%' then 'GP Query' end as 'Data Source Type'

,CASE WHEN [DataSourceType] LIKE '%Folder%' then f.SourceFolder WHEN [DataSourceType] like '%odbc%' THEN OD.OdbcDefaultId when [DataSourceType] like
'%Text%' then TF.[Path] when [DataSourceType] like '%Multi%' then MD.Name when [DataSourceType] like '%Excel2007%' then
E7.Workbook when [DataSourceType] like '%Excel2013%' then E13.Workbook END AS 'Data Source Location'
,[DATASOURCETYPE]
,convert(varchar,[CreatedDate],101) as 'Date Created'
,[CreatedUser] as 'Created By'
,convert(varchar,[ModifiedDate],101) as 'Date Last Modified'
,[ModifiedUser] as 'Modified By'
,[Owner] as 'Map Owner'
FROM [SmartConnect].[dbo].[Map] M left join
[SmartConnect].[dbo].[MsGpDestinationBase] D on M.[DestinationId] = D.[MsGpDestinationBaseId] left outer join
[SmartConnect].[dbo].[FolderDataSource] F on M.DataSourceId = F.FolderDataSourceId left outer join
[SmartConnect].[dbo].[MssqlDestinationBase] GP on M.[DestinationId] = GP.[MssqlDestinationBaseId] left outer join
[SmartConnect].[dbo].[OdbcDataSource] OD on M.DataSourceId = OD.OdbcDataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[TextFileDataSource] TF ON M.DataSourceId = TF.TextFileDataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[IntegrationGroup] IG ON M.IntegrationGroupId = IG.IntegrationGroupId LEFT OUTER JOIN
[SmartConnect].[dbo].[MultiDataSourceItem] MD ON M.DataSourceId = MD.[MultiDataSourceId] LEFT OUTER JOIN
[SmartConnect].[dbo].[Excel2007DataSource] E7 ON M.DataSourceId = E7.Excel2007DataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[Excel2013DataSource] E13 ON M.DataSourceId = E13.[Excel2013DataSourceId]

GO

——————————
Jo deRuiter
?? GPUG ALL STAR
??Granite Award Winner
Atlanta Chapter Leader
Aisling Dynamics Business Solutions
Business Applications Practice Manager
jo.deruiter@aislingdynamics.com
9518674366
aislingdynamics.com
——————————

[Jim Harris]

Wow – Christmas in June!  Thanks Jo.  You just knocked an item off my to-do list that I’ve been dreading.

——————————
Jim Harris
Director of Information Systems
ExamWorks, Inc.
Atlanta GA
——————————
——————————————-

[Tracy Hipps]

Hey Jo, I really liked your query, but noticed we had several large gaps in the information.  Upon review I noticed we had an Excel2003 and Excel 2010 destination base that was not included.  I thought I would help by adding those two destination bases so here is the updated query since you shared with all of us, I thought I might return the favor.

Enjoy!!

SELECT IG.IntegrationGroupName
,[MapId]
,[MapDescription] as ‘Description of Map’
,CASE WHEN [DestinationType] LIKE ‘%dYNAMICS.GP%’ THEN ‘GP ‘+d.[GroupName] when [DestinationType] like ‘%mssql%’ then gp.[DefaultMssqlConnection] when [DestinationType] like ‘%generic%’ then ‘Test File’ end as ‘Integrating To Destination’
,CASE WHEN [DestinationType] LIKE ‘%dYNAMICS.GP%’ THEN d.[NodeTypeName] when [DestinationType] like ‘%mssql%’ THEN GP.[MssqlTable] END as ‘Exactly Where in Desitnation’
,” as ‘Resulting Batch Name’
,[LastRunDate] as ‘Last Run on Date’
,[RunNumber] as ‘Latest Run Number’
,CASE WHEN [DataSourceType] LIKE ‘%Folder%’ then ‘Folder Data Source’ when [DataSourceType] like ‘%Multi%’ then ‘Multi Data Source’ when [DataSourceType] like
‘%Text%’ then ‘TXT or CSV Direct File’ when [DataSourceType] like ‘%odbc%’ then ‘SQL or ODBC Datasource’
when [DataSourceType] like ‘%Excel2007%’ then ‘Excel 07 Direct File’ when [DataSourceType] like ‘%Excel2010%’ then ‘Excel 10 Direct File’
when [DataSourceType] like ‘%Excel2003%’ then ‘Excel 03 Direct File’ when [DataSourceType] like ‘%Excel2013%’ then
‘Excel 13 Direct File’ when [DataSourceType] like ‘%GP%’ then ‘GP Query’ end as ‘Data Source Type’

,CASE WHEN [DataSourceType] LIKE ‘%Folder%’ then f.SourceFolder WHEN [DataSourceType] like ‘%odbc%’ THEN OD.OdbcDefaultId when [DataSourceType] like
‘%Text%’ then TF.[Path] when [DataSourceType] like ‘%Multi%’ then MD.Name when [DataSourceType] like ‘%Excel2007%’ then
E7.Workbook when [DataSourceType] like ‘%Excel2010%’ then E10.Workbook when [DataSourceType] like ‘%Excel2003%’ then E03.Workbook
when [DataSourceType] like ‘%Excel2013%’ then E13.Workbook END AS ‘Data Source Location’
,[DATASOURCETYPE]
,convert(varchar,[CreatedDate],101) as ‘Date Created’
,[CreatedUser] as ‘Created By’
,convert(varchar,[ModifiedDate],101) as ‘Date Last Modified’
,[ModifiedUser] as ‘Modified By’
,[Owner] as ‘Map Owner’
FROM [SmartConnect].[dbo].[Map] M left join
[SmartConnect].[dbo].[MsGpDestinationBase] D on M.[DestinationId] = D.[MsGpDestinationBaseId] left outer join
[SmartConnect].[dbo].[FolderDataSource] F on M.DataSourceId = F.FolderDataSourceId left outer join
[SmartConnect].[dbo].[MssqlDestinationBase] GP on M.[DestinationId] = GP.[MssqlDestinationBaseId] left outer join
[SmartConnect].[dbo].[OdbcDataSource] OD on M.DataSourceId = OD.OdbcDataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[TextFileDataSource] TF ON M.DataSourceId = TF.TextFileDataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[IntegrationGroup] IG ON M.IntegrationGroupId = IG.IntegrationGroupId LEFT OUTER JOIN
[SmartConnect].[dbo].[MultiDataSourceItem] MD ON M.DataSourceId = MD.[MultiDataSourceId] LEFT OUTER JOIN
[SmartConnect].[dbo].[Excel2007DataSource] E7 ON M.DataSourceId = E7.Excel2007DataSourceId LEFT OUTER JOIN
[SmartConnect].[dbo].[Excel2013DataSource] E13 ON M.DataSourceId = E13.[Excel2013DataSourceId] left outer join
[SmartConnect].[dbo].[Excel2010DataSource] E10 on M.DataSourceId = E10.Excel2010DataSourceId left outer join
[SmartConnect].[dbo].[Excel2003DataSource] E03 on M.DataSourceId = E03.Excel2003DataSourceId

——————————
Tracy Hipps
TD Carpenter & Associates, Inc.
——————————
——————————————-