Open Forum

  • 1.  Listing of All Map Data for SmartConnect (I'm giving this query to you!)

    GPUG ALL STAR
    Posted Jun 15, 2021 01:36 PM
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Listing of All Map Data for SmartConnect (I'm giving this query to you!)

    GOLD CONTRIBUTOR
    Posted Jun 16, 2021 07:27 AM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Listing of All Map Data for SmartConnect (I'm giving this query to you!)

    Posted 14 days ago
    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.
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role