Can i edit SSIS package file in Notepad and use it?

Question

I just opened a SSIS package file from the bin folder using notepad ++. The file opened in an xml syntax. Can i edit this file in notepad ++ and use it ? Or it needs some kind of compilation sort of thing after it has been edited ?

Answer 1

Editing an SSIS package

An SSIS package is just XML. So yes, you can edit it using your favorite text editor.

There are times editing the package via a text editor is faster and more efficient than using Visual Studio/BIDS/SSDT-BI. Say a size has increased from 30 to 50 for a string in a Data Flow and you have a Union All in there. The components upstream from the Union All will "hear" the length change but the components downstream of the Union All will be stuck at 30. The only way to fix it in VS is to delete the column from the Union All (and all the downstream consumers of it) and then readd it. And potentially re-add it to all of them.

That said, it's very easy to make a mistake and end up with a package that is FUBARed. But since you use version control (I don't care what, just that you use something) you can easily roll back to a known good state.

Compiling an SSIS package

Compiling an SSIS package isn't really a "thing". From a language perspective, the XML inside an SSIS package is already compiled to the byte code level. That's a generous statement, I admit but in short, there's no further compilation that takes place. The package is interpreted and sure, maybe those instructions are further optimized but there is no secret sauce that takes place inside the editing of a package that results in a more "compiled" than what is written by hand.

<?xml version="1.0" encoding="utf-8"?>
<DTS:Executable DTS:CreationDate="09/12/2015 11:28:01" DTS:CreationName="SSIS.Package.3" DTS:CreatorComputerName="RHUDAUR" DTS:CreatorName="bfellows" DTS:DTSID="{F003ACF1-6676-485A-9E3E-568DFE7FCE55}" DTS:ExecutableType="SSIS.Package.3" DTS:LastModifiedProductVersion="" DTS:LocaleID="1033" DTS:ObjectName="HelloWorld" DTS:ProtectionLevel="0" DTS:refId="Package" DTS:VersionBuild="0" DTS:VersionGUID="{83703EB8-3F47-4F50-9992-CF0AAA544FAD}" DTS:VersionMajor="0" xmlns:DTS="www.microsoft.com/SqlServer/Dts">
  <DTS:Property DTS:Name="PackageFormatVersion">6</DTS:Property>
</DTS:Executable>

The above is the XML that defines a 2012 SSIS Hello World package. It does nothing, doesn't even print Hello World but that's ready for the interpreter.

As I stated in the next section, if this is the Project Deployment Model, then there is a compilation step, of sorts. An SSIS project there contains the Packages + Project level parameters (even if empty) + Project Level Connection Managers (if exists). All of those things must exist, plus a manifest file enumerating the contents of a project and a type file (standard file across all projects). All of that junk is zipped up into a file called an .ispac file. The creation of that .ispac file goes beyond what you can accomplish in a text editor using normal means.

This is my project as seen from Visual Studio

This is what the resulting .ispac file looks like

Using an SSIS package

When Visual Studio/BIDS/SSDT-BI runs a package it uses dtexec.exe. Technically, it uses DtsDebugHost but that only works from within the context of Visual Studio. If you want to "use" the SSIS package from the text-editor-of-choice, you will need to link to the dtexec location (either x86 or program files depending on drivers used) and set up a parameter like /file %1 where %1 represents the current file name.

If you're using the Project Deployment Model, new in 2012, then this becomes a much more complicated endeavor. You'd need to compile the current file into an .ispac file along with the project.params file and any project level connection managers and then run the package from the context of the ispac. Sample parameter list would look like /package %1 /project %2 where %2 is the ispac file.