Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 493

@ApplicationID, @PackageID, @ExecutionOrder");
sb.AppendLine(" print @PackageName + ' added and wired
to ' + @ApplicationName");
sb.AppendLine(" end");
sb.AppendLine("Else");
sb.AppendLine(" print @ApplicationName + '.'
+ @PackageName + ' already exists in the Framework with
ExecutionOrder ' + convert(varchar, @ExecutionOrder)");
sb.AppendLine();
e += 10;
}
This code inside the loop executes for every package contained in the tables collec-
tion populated from the call against the connection object's GenerateTableNodes()
method. Each iteration begins with a comment to identify the name of the SSIS pack-
age followed by the setting of the PackageName and ExecutionOrder parameter values.
PackageName is set to the name of the SSIS Package contained in the string variable
tbl; ExecutionOrder is set to the string version of the integer value contained in the e
variable.
Next, a conditional statement which checks for the existence of a package in the
Framework is added to the StringBuilder variable, “sb.” If the package metadata is not
present, it is added by executing the cfg.AddSSISPackage stored procedure which re-
turns the PackageID parameter. If the package metadata is present, a statement populat-
ing the PackageID parameter is executed.
The next portion of the Transact-SQL added to the stringbuilder is a similar section
which associates SSIS Package and Application metadata. This is accomplished by
adding rows to the cfg.AppPackages “bridge” table - or informing the user that this re-
lationship already exists.
The final statement of the loop increments the value of the variable e by 10.
The last section of BimlScript writes the contents of the stringbuilder to the file
specified in the i-FrameworkSQLGen.biml file with the filename variable, as shown in
Listing 20-9 :
Listing 20-9 . Writing the Transact-SQL in the Stringbuilder to the Output File
using (StreamWriter outfile = new StreamWriter(filename))
{
outfile.Write(sb.ToString());