Overwritten changes in Git with Azure DevOps

Viewed 165

My team is experiencing trouble with Git in Azure DevOps. They use SSIS package and the development is done through Visual Studio (VS). They also do their commits through VS (From my side, I use Git Bash). Their implemented changes are often overwritten during branch merges, which is obviously a very dangerous behavior: enter image description here I am not familiar at all with SSIS and I don't understand this behavior at all. I am an R and Python user and as far as I know, I only experience Git conflicts. What is the cause of this behavior? How is it possible that branches are created if we don't specify it (through git branch)? Is it the combination of VS and SSIS that makes version control with Git unpredictable? Should they go for using TFVC instead of Git, knowing the fact that Microsoft does not recommend it (BTW, why? It does not give an clear explanation)

1 Answers

Not a git expert, but I know something about SSIS.

An SSIS package is an XML serialization of Integration Services objects. It also, unfortunately, contains presentation data to support the layout of the canvas for the design experience.

Assume the following defines an SSIS package that truncates a table and you check that in.

<SSIS>
    <TASKS>
        <EXECUTE_SQL_TASK COMMAND="TRUNCATE TABLE dbo.T1;"/>
    </TASKS>
    <LAYOUT>
        <EXECUTE_SQL_TASK X="100" Y="200" HEIGHT="300" WIDTH="50" />
    </LAYOUT>
</SSIS>

The next developer opens the package and resizes the Task. No semantic change occurs but the serialized version of the package does change.

<SSIS>
    <TASKS>
        <EXECUTE_SQL_TASK COMMAND="TRUNCATE TABLE dbo.T1;"/>
    </TASKS>
    <LAYOUT>
        <EXECUTE_SQL_TASK X="50" Y="200" HEIGHT="300" WIDTH="150" />
    </LAYOUT>
</SSIS>

At the same time, developer #2 has a structural change to make to the package, so they create this representation of the original package

<SSIS>
    <TASKS>
        <EXECUTE_SQL_TASK COMMAND="TRUNCATE TABLE dbo.T2;"/>
        <EXECUTE_SQL_TASK COMMAND="TRUNCATE TABLE dbo.T1;"/>
    </TASKS>
    <LAYOUT>
        <EXECUTE_SQL_TASK X="100" Y="200" HEIGHT="300" WIDTH="50" />
    </LAYOUT>
</SSIS>

The last person to check in their change is going to encounter a merge conflict. No version control tool handles XML merging gracefully (at least rcs, cvs, svn, SourceSafe, TFS, git do not)

Mentally, yes you can handle merging the sample XML but the actual XML that represents SSIS is stupidly complex. After having been deep in the bowels of SSIS for 15+ years, I might be able to modify the XML by hand to resolve a merge conflict and even then it's going to be highly version dependent (2012+ only please).

To a lesser extent, the same problem happens in a .NET project which is also XML based (MyProject.csproj). If you add a new class and another developer also adds a new class, you can get a merge conflict if you both checked in your project at the same time.

What should I do

The following is a set of good practices I encourage

Communicate

Developer A adds packageSales to the project and Developer B adds packageCustomer to the project and both commit changes to source control, one of them will likely "lose" their package because XML merging doesn't happen. The package is still in the file system, it's just that the list of known packages that the .dtproj file used by Visual Studio was merged from

<PACKAGES>
    <PACKAGE Name="packageSales.dtsx"/>
</PACKAGES>

to

When it really should have "merged" as

<PACKAGES>
    <PACKAGE Name="packageSales.dtsx"/>
    <PACKAGE Name="packageCustomer.dtsx"/>
</PACKAGES>

Large projects with multiple, concurrent SSIS developers, anoint one person to be in charge of adding packages to the project. Have them add a blank package and commit the updated package and project file to version control and then let the rest of the developers know to get the latest version from source control.

Treat SSIS packages as binary objects

It will be better to think of an SSIS package as a binary object. There is no reason or value in attempting to produce a diff to identify what changed between versions as there is too much "noise". Every time you save, even if you've made no change to the package, two fields are always updated: VersionNumber and VersionGuid (names approximate)

I have even marked SSIS as binary objects in assorted version control systems as a merged package is all but guaranteed to be a broken representation of the XML package dialect.

Smaller is better

Since we're already thinking of packages as binary objects and system generated difference files won't help identify what changed, create business process focused SSIS packages. Replace "Load Data" which ran N tasks with "Load Sales", "Load Customer" and maybe "Load Data" remains but it simply orchestrates the running of the Sales and Customer packages. Because we're making more packages but smaller in scope, that allows you to distribute the work to multiple developers but without the risk of merge conflicts in packages.

Rethink your approach

Depending on how repeatable your development is, while the XML that represents an actual SSIS package is overly complex, Biml is not. Biml is a domain specific language that describes tables and packages. The beauty of it is that it's small enough to describe exactly everything you can do in SSIS but without all the cruft in the actual XML.

Here's a hello world level SSIS package as expressed in Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="SimpleBimlPackage" />
    </Packages>
</Biml>

Run that through the free Visual Studio add on BimlExpress and it will create an SSIS package with nothing in it.

The following is a non-trivial bit of Biml that describes an SSIS package with two OLEDB connection managers. It has a variable called RowCountNew. It has a data flow which brings data from Adventureworks Sales.Currency table, counts the number of rows in the pipeline and then lands it in a table called RAW.SalesCurrency and maps the original column of Name to a destination of CurrencyName

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
    <OleDbConnection 
        ConnectionString="Data Source=localhost\dev2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="AdventureWorks"/>
    <OleDbConnection 
        ConnectionString="Data Source=localhost\dev2012;Initial Catalog=TypeMoreClickLess;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="TypeMoreClickLessLocal"/>
    </Connections>
    <Packages>
        <PackageName="HelloDataFlow"
            <Variables>
                <Variable Name="RowCountNew" DataType="Int32" Namespace="User">0</Variable>
            </Variables>
            <Tasks>
            <Dataflow Name="DFT Basic Dataflow">
                <Transformations>
                    <OleDbSource Name="OLE_SRC Demo"
                        ConnectionName="AdventureWorks" >
                        <DirectInput>
                        SELECT SC.CurrencyCode, SC.Name
                        FROM Sales.Currency AS SC;
                        </DirectInput>
                    </OleDbSource>
                    <RowCount Name="CNT New"
                        VariableName="User.RowCountNew"/>
                    <OleDbDestination
                        Name="OLE_DST RAW_SalesCurrency"
                        ConnectionName="TypeMoreClickLessLocal"
                        KeepIdentity="false"
                        TableLock="true"
                        UseFastLoadIfAvailable="true"
                        KeepNulls="true"
                    >
                        <ExternalTableOutput Table="[RAW].[SalesCurrency]"/>
                        <Columns>
                            <Column SourceColumn="Name" TargetColumn="CurrencyName" />
                        </Columns>
                    </OleDbDestination>
                </Transformations>
            </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>    

That is much easier for a developer to understand. And much easier to automate than all the mouse clicks involved with SSIS.

As an added bonus, you can reverse engineer existing SSIS packages into Biml. Which can then make the prospect of manually identifying what changed between two sets of SSIS packages a less Herculean feat and something a mere mortal can handle.

Related