메뉴
Use when explaining or reasoning about the contents of an SSIS .dtsx file — what each element means, which IDs the managed object model owns, and why hand-editing the XML breaks designer-load and execution. Reference, not a recipe.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
Use to diagnose `dtexec /Validate` exit codes and output when Test-SsisPackage.ps1 reports FAIL. Maps the common DTSER_/DTS_E_ error patterns to root cause (metadata error vs. pattern-module bug vs. environment issue) and the file the agent should edit to fix it (never the .dtsx).
Use as Step 4 of the ssis-delivery-gate. Performs a clean `git clone` of the current HEAD into a temp directory and re-runs Test-SsisPackage + Test-SsisDesignerLoad + Build-SsisIspac against the cloned copy, proving the artifact survives the round-trip a downstream developer would experience. Catches line-ending corruption, missing tracked files, and accidental dependence on uncommitted state.
Use to run the non-bypassable delivery gate after any SSIS-affecting change. Encodes the canonical sequence of primitive invocations (Test-SsisPackage → Test-SsisDesignerLoad → Build-SsisIspac → ssis-clone-roundtrip) and the triage hand-off on failure. Always invoked by ssis-validator, optionally by ssis-author during exploration.
Use when authoring SSIS packages, metadata JSON, or DDL that reads from AdventureWorks2025. Pins the canonical source-table → demo-staging mapping so the agent never invents column names. Required reading before any /generate-*-package prompt that targets the demo schema.
Use when deploying an .ispac to SSISDB and executing packages via catalog stored procedures (catalog.deploy_project, catalog.create_execution, catalog.start_execution). Covers folder/project/environment lifecycle, parameter overrides, and status polling.
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.
SOC 직업 분류 기준
| name | dtsx-xml-anatomy |
| description | Use when explaining or reasoning about the contents of an SSIS .dtsx file — what each element means, which IDs the managed object model owns, and why hand-editing the XML breaks designer-load and execution. Reference, not a recipe. |
.dtsx is a Microsoft-defined XML format (MS-DTSX2 spec) that captures everything an SSIS designer needs to recreate the package: control flow, data flow, connections, parameters, configurations, protection level, layout. It is the SSIS designer's serialization format — the designer holds an object model in memory while you edit, and .dtsx is what it writes to disk.
The agent never hand-edits .dtsx. So why have this skill? Three reasons:
dtexec or designer error message ("refId Package\… not found") to the structural cause.<DTS:Executable DTS:refId="Package" DTS:ExecutableType="Microsoft.Package" …>
<DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
<DTS:Property DTS:Name="ProtectionLevel">0</DTS:Property> <!-- 0 = DontSaveSensitive -->
<DTS:Property DTS:Name="DTSID">{GUID}</DTS:Property>
<DTS:ConnectionManagers> … </DTS:ConnectionManagers>
<DTS:Variables> … </DTS:Variables>
<DTS:Executables> … control flow tasks … </DTS:Executables>
<DTS:PrecedenceConstraints> … </DTS:PrecedenceConstraints>
<DTS:DesignTimeProperties> … layout, only used by VS designer … </DTS:DesignTimeProperties>
</DTS:Executable>
PackageFormatVersion 8 = SQL Server 2017+; SQL Server 2022 / 2025 also write 8. ProtectionLevel 0 = DontSaveSensitive.
| Attribute | Owner | Effect of manual edit |
|---|---|---|
DTS:refId | OM | Breaks all cross-references inside the package (precedence constraints, parameter bindings, expressions). |
DTS:DTSID ({GUID}) | OM | Breaks SSISDB execution history correlation; sometimes designer-load. |
lineageId (on data-flow paths and columns) | Data-flow OM | Breaks data-flow column mapping; runtime errors like "no input column found for output column with lineage id N". |
<DTS:Property DTS:Name="ObjectName"> GUID-suffixed values | OM | Designer may rebuild correctly OR may corrupt — never edit. |
The <DTS:DesignTimeProperties> block stores designer layout (x/y/width/height of each shape in the canvas). It is VS-designer-only; runtime ignores it. Diffs here are cosmetic noise. But: don't delete the block — VS will regenerate it but a missing block can prompt an "unsaved layout" dialog.
Data flow tasks contain a <pipeline> element with one <component> per source/transform/destination. Each <component> has <inputs> and <outputs>, each with <inputColumns> and <outputColumns> that reference lineageIds on prior outputs. This is the most fragile part of the file. The OM threads lineageIds across the entire data flow; hand-edits typically break it silently (no XML schema violation, but runtime errors).
| Layer | Tool |
|---|---|
| XML well-formedness | [xml]$_ = Get-Content <pkg>.dtsx in PowerShell, or XmlReader.Read |
| Schema (XSD) | dtsxschema.xsd ships with SSIS; rarely used standalone — dtexec /Validate covers it |
| Runtime validation | dtexec /File <pkg>.dtsx /Validate /WarnAsError |
| Designer load | [Microsoft.SqlServer.Dts.Runtime.Application]::new().LoadPackage(<pkg>.dtsx, $null) |
The toolkit's Test-SsisPackage.ps1 runs the runtime-validation step (dtexec /Validate /WarnAsError); Test-SsisDesignerLoad.ps1 covers designer-load.
Application.LoadPackage: https://learn.microsoft.com/dotnet/api/microsoft.sqlserver.dts.runtime.application.loadpackage