ETL and Data Integration tools - Modern ETL software for everyone

Do you have any reporting capabilities from the repository?
I need some reports on Packages that have been created.

Charles

Charles

If you tell us what are you looking for we can help you to write sql to create reports

Mike

We have all of our ETL logic for loading our data mart in the VI tool.
We need a report we can run on a package (and of course all it’s subpackages), that list the SQL and descriptions in order from beginning to end.

Charles

Charles

Sorry for the delay

Package is a text file stored in the blob field therefore it is not possible at the momenet to write an sql to extract data from packages.

However if the package was executed it should be possible to extract some data from the QUEUE_ACTIONS ,OBJECTS and OBJECTS_TREE tables provided that object/script name is unique

Mike

Charles

Here are two SQL Statements
Number one will return list of all sql scripts:

SELECT OBJECTS_TREE.NAME, OBJECTS.OBJECT_DATA, OBJECTS.F1, OBJECTS.COMMENT, OBJECTS.CREATED, OBJECTS.CREATED_BY, OBJECTS.CHANGED, OBJECTS.CHANGED_BY, CONNECTIONS.DATABASE_NAME, CONNECTIONS.SERVER_NAME
FROM (CONNECTIONS INNER JOIN OBJECTS_TREE ON CONNECTIONS.OBJECT_ID = OBJECTS_TREE.PARENT_ID) INNER JOIN OBJECTS ON OBJECTS_TREE.OBJECT_ID = OBJECTS.OBJECT_ID
WHERE (((OBJECTS_TREE.OBJECT_TYPE)=2));

Number two will return list of sql scripts for particular execution
You may need to change QUEUE_ID from 747 to something else

SELECT OBJECTS_TREE.NAME, OBJECTS.OBJECT_DATA, OBJECTS.F1, OBJECTS.COMMENT, OBJECTS.CREATED, OBJECTS.CREATED_BY, OBJECTS.CHANGED, OBJECTS.CHANGED_BY, CONNECTIONS.DATABASE_NAME, CONNECTIONS.SERVER_NAME, QUEUE_ACTIONS.QUEUE_ID
FROM ((CONNECTIONS INNER JOIN OBJECTS_TREE ON CONNECTIONS.OBJECT_ID = OBJECTS_TREE.PARENT_ID) INNER JOIN QUEUE_ACTIONS ON OBJECTS_TREE.NAME = QUEUE_ACTIONS.ACTION_NAME) INNER JOIN OBJECTS ON OBJECTS_TREE.OBJECT_ID = OBJECTS.OBJECT_ID
WHERE (((QUEUE_ACTIONS.QUEUE_ID)=747) AND ((OBJECTS_TREE.OBJECT_TYPE)=2))

I tried to create MS Acces reports first but could not because access does not show OBJECT_DATA field correctly. I suggest to use different reporting tool such as BO or Cristal reports

Mike