Whilst working with one of our SAP Data Services clients this week we came across an issue with regard to the local and central repositories on their development server. Now there have been a few migrations and various shenanigans with regard to moving the data around so I am not that surprised we ended up in the situation that we did. It was however a situation that I’ve not seen before and so I wanted to blog it (as the guy who engaged me with blogging said, If I don’t write it down I’ll just forget it). It is of course a great way to engage and satisfy the wider community too.
Now, I must admit that I’m not really a great fan of the SAP Data Services version control system. Personally in my professional lifetime which has come from ‘proper software development’ (which if I’m a good boy they still let me do….) we’ve come a long from Visual Source Safe (off of the 90’s); The offerings out there currently whilst being relatively complex do offer a great deal more flexibility; In house for ‘proper development’ projects we use git related systems, BitBucket until fairly recently but now we are back in the Microsoft fold using the DevOps offering with all the goodies that brings. I’m also a hard and fast devotee of the Gitflow methodology. But of course the SAP internal source control system is none of these, its much more like Source Safe than I would care to mention. It seems at once both too rigid and too lenient, a bit of a ‘Schrodingers Cat’ amongst source control systems. It is however perfectly usable, especially with discipline, and a lot better than the alternatives…these being:-
- Having No Source Control
- Backing Up ATL files
As a wise-man once said, if you don’t have source control.. you don’t have any code. Well, I’m not sure if he was wise but he seemed pretty clever to me. I know a great many people who have found this out to their cost. An ex colleague of mine from a company I was working for in the 90’s once told me that from his house he had once written and hosted the original website for a (still) famous book retailer. This was back when websites were a bit…. niche! He found out to his cost that not having a copy of that source is a little bit ‘character building’ when thieves broke in and stole the only thing of value in his flat, the webserver complete with the only copy of the source code.
Still… I always think its easier to write these things the second time around.
Returning to the subject in hand, and our customers issues, we found that the code in their local development repository, which we had brought in as an ATL import, appeared to be marked as checked out for reasons unknown the client and to us. This presents a problem to SAP Data Service as you can only check-in code that is not checked out. So lets take a look at a simple, sample project I have used to demonstrate the issue:- The following items have been marked as checked out to another repository, in this instance a bogus one (I did this manually for the purposes of this blog):-
- PropertyData Project
- ImportPropertyData Job
In addition below these items the following workflows and data flows have also been checked out in the same way:-
- CreateWarehouse workflow
- CreateFacts dataflow
And just for a ‘full house’ we have also managed to check out the following data Store objects:-
Now, just for arguments sake lets see what we could do to correct this issue using the UI before fixing this relatively easily. We could try checking in right?
OK, Maybe we could try checking out (without overwriting any local changes we have)
OK, Last bit of barnstorming mad desperation… We could just check in, or or or… we could just undo the check out (In this instance the project is already in the central repository)
We instead need to understand what actually happens to put these items into a checked in state, which for once is ridiculously simple. There are four main database tables that contain all of the user objects within the Data Services domain. these being:-
- AL_LANG which contains Jobs, Workflows and Dataflows.
- ALFUNCINFO – Which contains information about custom functions
- AL_PROJECTS -Which contains, of course, project objects
If you take a look in each of these tables there are three columns that control the check out status of an object these being:-
- state – 1 indicates checked out, 0 the opposite
- checkout_dt – indicates the date on which the checkout was performed
- checkout_repo – indicates the name of the repo into from which the checkout was performed
Given this fact that makes these fields super easy to manipulate IF WE GET INTO TROUBLE. (I cannot stress that enough; This blog is only intended for corrective actions where something has gone a little bit awry, its not for everyday use!) Lets take a look at the data for the objects that we have checked out which is stored (in this case) in a MySQL Repository:-
As you can see from these SQL results that the Items we are interested in all have a value in the 3 columns we spoke about earlier, my feeling is that the state column alone that controls all of this but when correcting the data I would always recommend updating all 3 of these fields to keep the data consistent.
Thus the simple solution to allow our projects to interact with our central repository is thus to remove this check out information so that the system now thinks that those objects are no longer undergoing source control edits, thus the following update statements should suffice to return the system to correct functionality:-
After running these statement you should now see that, upon clicking ‘Refresh Object Library’ in the Data Services Designer your items no longer appear checked. This means that if they are not currently under source control you should be able to add them to your central repository. In my instance however, they were already under source control, so whilst I can’t add them I can now check them out and start editing them, safe in the knowledge that any changes made can now be committed back to source control.