git-roundtrip-for-ssis

name	git-roundtrip-for-ssis
description	Use when explaining or debugging why an SSIS project must clone-rebuild-load cleanly on a fresh Windows machine. Covers .gitattributes invariants, the Verify-ClonedProject gate, the most common round-trip failures, and how the toolkit prevents each.

Git round-trip for SSIS

The toolkit guarantees: a fresh git clone on a clean Windows + Visual Studio 2026 (18.4+) machine, with the SSIS Projects extension installed, can open the .dtproj, build via SSISBuild.exe, and execute the .ispac with zero manual repair steps. This is what makes the repo usable by both VS-designer teams and VS Code agentic teams from one source tree.

When to load this skill

• Reviewing a PR that touches .gitattributes, .gitignore, or tools/Verify-*.ps1.
• Investigating a round-trip failure (Verify-ClonedProject.ps1 exit non-zero).
• Explaining to a user why their hand-edit broke designer-load on someone else's machine.

The gate (Verify-ClonedProject.ps1)

Runs four checks in order against a temp-dir clone:

1. git clone --depth=1 into a sibling temp directory. Must succeed.
2. SSISBuild.exe -p:<.dtproj> -ss — strips sensitive data (-ss) and produces .ispac. Exit code 0.
3. Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage against every .dtsx. Must not throw.
4. dtexec /Project <.ispac> /Package <name> /Validate /WarnAsError against every package. Must succeed.

Any failure = PR is blocked.

.gitattributes invariants

*.dtsx   text eol=crlf
*.dtproj text eol=crlf
*.conmgr text eol=crlf
*.params text eol=crlf
*.ispac  binary
*.ps1    text eol=lf
*.sql    text eol=lf
*.md     text eol=lf
*.json   text eol=lf

Why CRLF for SSIS files: SSIS XML is generated by Visual Studio on Windows. The SSIS runtime tolerates LF reads, but Visual Studio's designer rewrites the file on first save with CRLF. If a Linux/macOS clone normalizes to LF, the next Windows checkout shows a noisy diff on every file you open — a usability disaster. CRLF-pinned via .gitattributes keeps the file byte-identical across platforms.

Why binary for .ispac: it's a ZIP. Don't normalize.

.gitignore invariants

Must exclude:

• bin/, obj/, .vs/ — VS / MSBuild scratch
• *.user, *.suo, *.dtproj.user — per-user UI state
• out/, *.ispac — build artifacts
• .env* (except .env.example)

Must not exclude .vscode/ — the recommended extensions, tasks, and MCP config ship in the repo.

Common failures and fixes

Symptom	Root cause	Fix
Verify-ClonedProject.ps1 step 2 fails: "Could not load package 'X.dtsx': Could not find a part of the path …\bin\…"	.dtproj references generated paths that don't exist post-clone	Build is fine; this is a bin/ reference inside .dtproj. Regenerate the project skeleton.
Step 3 throws System.Xml.XmlException: '0x16' is an invalid character at line 1 col 1	UTF-8 BOM lost or wrong encoding	.dtsx must be UTF-8 with BOM. Regenerate via Save-SsisPackage; Package.SaveToXml writes BOM correctly.
Step 3 throws Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to decrypt protected XML node "DTS:Password"	ProtectionLevel != DontSaveSensitive and encrypted nodes leaked	Regenerate; the generator enforces DontSaveSensitive uniformly.
Step 4 fails: Error 0xC020207F: SSIS.Pipeline: Lineage ID N is not a valid input column lineage ID	Data-flow IDs corrupted by manual edit	Regenerate from metadata JSON. The OM owns lineage IDs.
Designer opens project, then immediately prompts "Convert to Project Deployment Model"	Legacy package deployment model leaked into commit	Re-scaffold — toolkit only supports Project Deployment Model.
First open in Visual Studio shows hundreds of layout diffs in <DTS:DesignTimeProperties>	EOL got normalized to LF	Confirm .gitattributes and core.autocrlf settings; reclone.

Local config that must NOT be committed

• .env, .env.local — credentials for SSISDB password parameters.
• Anything under .vscode/*.local.json — per-user MCP input cache, etc.
• *.user, *.dtproj.user — VS user settings.

The toolkit's .gitignore covers these. Verify it before opening a PR.

Cross-machine consistency

The .gitattributes and .gitignore together cover normalization, exclusion, and binary identification. After cloning to a new machine:

git config core.autocrlf false   # let .gitattributes drive everything
git config core.eol native       # safety net
git rm --cached -r . && git reset --hard   # re-apply attributes if cloned with wrong autocrlf

References

• gitattributes: https://git-scm.com/docs/gitattributes
• SSISBuild: https://learn.microsoft.com/azure/devops/pipelines/tasks/reference/ssis-build-v0
• Application.LoadPackage: https://learn.microsoft.com/dotnet/api/microsoft.sqlserver.dts.runtime.application.loadpackage
• dtexec /Validate: https://learn.microsoft.com/sql/integration-services/packages/dtexec-utility