Hi,
We use BU's and some sources are created in another TX project.
How can I verify / check the dependency of a source in this case a SQL table?
For example I have in the BU a SQL table (using global database) that is created in another TX project.
How can I check which TX project has created this table?
And also in general, how can I create a dependency list of a TX projects (dependency with other TX projects)?
Is this stored in the repository?
Regards,
Lorenzo
Best answer by Thomas Lind
For reference this was the suggested query
The following query can be run on the repository to show which project a data table is associated with.
SELECT TOP (1000) [DataTableId] ,dt.[DataSourceId] ,p.[ProjectId] ,bu.[BusinessUnitId] ,ds.[Name] AS DataSourceName ,dt.[Name] AS DataTableName ,bu.[Name] AS BusinessUnitName ,p.[Name] AS ProjectName ,[SchemaName] ,[StagingName] ,[OrigName] ,[IsChecked] ,[TableType] ,[ExternalProperties] ,dt.[ValidFrom] ,dt.[ValidTo] FROM [TxRepository].[dbo].[DataTables] dt LEFT OUTER JOIN [dbo].[DataSources] ds ON ds.[DataSourceId] = dt.[DataSourceId] LEFT OUTER JOIN [dbo].[BusinessUnits] bu ON ds.[BusinessUnitId] = bu.[BusinessUnitId] LEFT OUTER JOIN [dbo].[Projects] p ON p.[ProjectId] = bu.[ProjectId] -- WHERE dt.[StagingName] like '%%' ORDER BY dt.[StagingName]You can uncomment out the WHERE statement and enter a name to search for a specific data table.
+5Hi
As mentioned in other locations, you can use the repository database to find what you look for.
Why are you using another project as a source to a project, why can’t this part be in this project?
You can add external execution packages from the other project and use that to run before the tables that uses this source is executed. You can’t schedule any deploy operations, so you will have to do this manually in both projects.
+5For reference this was the suggested query
The following query can be run on the repository to show which project a data table is associated with.
SELECT TOP (1000) [DataTableId] ,dt.[DataSourceId] ,p.[ProjectId] ,bu.[BusinessUnitId] ,ds.[Name] AS DataSourceName ,dt.[Name] AS DataTableName ,bu.[Name] AS BusinessUnitName ,p.[Name] AS ProjectName ,[SchemaName] ,[StagingName] ,[OrigName] ,[IsChecked] ,[TableType] ,[ExternalProperties] ,dt.[ValidFrom] ,dt.[ValidTo] FROM [TxRepository].[dbo].[DataTables] dt LEFT OUTER JOIN [dbo].[DataSources] ds ON ds.[DataSourceId] = dt.[DataSourceId] LEFT OUTER JOIN [dbo].[BusinessUnits] bu ON ds.[BusinessUnitId] = bu.[BusinessUnitId] LEFT OUTER JOIN [dbo].[Projects] p ON p.[ProjectId] = bu.[ProjectId] -- WHERE dt.[StagingName] like '%%' ORDER BY dt.[StagingName]You can uncomment out the WHERE statement and enter a name to search for a specific data table.
Hi Thomas,
Unfortunately this will not work.
I can find the link to the DataSource in DataTables (TableType = ‘DataSource’) but I can't find the relation in which Project this table is created.
Probably this should be the row with TableType = ‘DataWarehouse’ but the SchemaName does not match in my case and can't find any repository table that stores the relation between the table created in the Datawarehouse of a specific project and the above mentioned table found in the DataTables.
See below query:
SELECT DataTableId, DataSourceId, SqlServerConnectionId
, [Name], SchemaName, StagingName, OrigName, TableType
FROM [dbo].[DataTables] dt
WHERE ValidTo = 99999999
AND (dt.[StagingName] like '%FreshdeskTickets%'
OR dt.[OrigName] like '%FreshdeskTickets%'
OR dt.Name like '%FreshdeskTickets%')
Remark: ValidTo is needed else you have to many matches
Regarding your question why it is not created in one project, we have some legacy where some tables are created and are useful to use as a source in another project (which are completely different topics) and we would not want to use the same logic in two different projects.
To be able to find where the source is created I would automatically want to retrieve the info instead of manually check the projects.
+5Hi
+5Hi
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
