The Update procedure
This update adds the Cloud Storage connection object types to the repository. Run it only after backing up the repository and confirming that you are updating the correct database. Repository changes are not a good place for freestyle SQL. That way lies drama, and usually a very quiet office.
Advanced ETL Processor and Visual Importer ETL store connection definitions in the repository database. Older repositories may not contain the object types needed for Cloud Storage connections.
The SQL below creates the Cloud Storage connection types, adds the Cloud Storage node under the relevant part of the object tree, and assigns the object type group used by the designer.
Before you run the SQL
- Create a repository backup.
- Stop any services using the repository.
- Run the SQL against the repository database, not a production data warehouse or source system.
- Start the software and check that Cloud Storage connections are available.
Run this SQL against the repository
Insert into OBJECT_TYPES
(OBJECT_TYPE,OBJECT_TYPE_NAME)
Values
('905','Cloud Storage Connection')
Insert into OBJECT_TYPES
(OBJECT_TYPE,OBJECT_TYPE_NAME)
Values
('906','Cloud Storage Connections')
INSERT INTO OBJECTS_TREE
(OBJECT_ID,
PARENT_ID,
NAME,
OBJECT_TYPE
)
Select
OBJECT_ID*-4 as OBJECT_ID,
PARENT_ID,
'Cloud Storage' as NAME,
905 as OBJECT_TYPE
From
OBJECTS_TREE
where OBJECTS_TREE.OBJECT_TYPE=322
UPDATE OBJECT_TYPES
set OBJECT_TYPE_GROUP=24000
WHERE OBJECT_TYPE= '905'After the repository update
Restart the application and check the connection tree. Cloud Storage should now appear as a connection option.
If the repository still shows the old structure, confirm that the SQL was run against the active repository. This is the usual culprit. The wrong database can look very innocent while wasting a perfectly good afternoon.