Press "Enter" to skip to content

Category: Integration Services

Drawing SSIS Packages

Published 2019-08-07 by Kevin Feasel

Bartosz Ratajczyk continues a quest to draw SSIS packages as SVGs:

To get the Value and Expression properties I need to find the precedence constraint in the .dtsx file during the XSL transformations. It requires three changes in the package2svg.xsl:

– I have to pass the name of the .dtsx file
– I have to read the XML from the .dtsx file
– I have to use the DTS namespace because it’s the namespace of the .dtsx file

Read on for more. Bartosz to this point has covered the control flow.

Comments closed

Drawing SSIS Packages as SVGs

Published 2019-07-16 by Kevin Feasel

Bartosz Ratajczyk continues a series on taking SSIS packages and generating SVGs from their control flows:

To make things harder, the layout of the sequences and tasks is not some nested XML structure. All of the elements have the same parent – <GraphLayout>, meaning all of them are at the same tree level. Also – there is no attribute showing where a particular object belongs. Almost. In the example with the sequences, I see two regularities:
– the outer container is placed later in the XML, than the inner container
– the @Id attributes show the nesting of the objects

I’m not sure how often I’d use this in practice, but if you want to understand some of the internals of SSIS, this is an interesting series to follow.

Comments closed

Executing Azure SSIS Packages from Blob Storage

Published 2019-07-02 by Kevin Feasel

Andy Leonard cranks it to the next level:

I confess: I have been waiting for this feature since I first learned of Azure-SSIS.

When I first saw Azure-SSIS – which creates an Azure Data Factory Integration Runtime and SSIS Catalog in the cloud, my first thought was a paraphrase Ferris Bueller’s question about dying the river green: “If we can execute SSIS packages from the SSIS Catalog in Azure Data Factory, why can’t we execute SSIS packages from Azure Blob Storage?” Today, we can.

Read on to see how you can do it.

Comments closed

Converting Existing SSIS Packages to Biml

Published 2019-05-16 by Kevin Feasel

David Stein shows off a conversion tool built into BimlExpress:

BimlExpress is a free Visual Studio add-in created by the good folks at Varigence. Its a full featured Biml editor which allows you to dynamically create SSIS packages. It was first released back in 2017, and the latest version is 2019 (of course). The current version supports Visual Studio 2010 through 2019 as well as SQL Server 2005 through 2019.

Prior to it’s release, Biml was written with Bids Helper, now known as BI Developer Extensions. While BI Developer Extensions has many nice features, you should no longer use it to work with Biml as it is no longer being updated/supported.

I’m pleasantly surprised by this. It used to be limited to BimlStudio (nee Mist) and BimlOnline.

Comments closed

SSIS 2019 Preview Released

Published 2019-05-06 by Kevin Feasel

Koen Verbeeck notes something very nice:

It is not a joke: SSIS is available for Visual Studio 2019 as a preview. Whoa, hold on. SQL Server 2019 hasn’t been released yet? But there’s already an SSIS 2019? Didn’t we have to wait months after the release of SQL Server 2017 before we had an SSIS version for Visual Studio 2017?

Yes, we did, you can read all about there here.

But times have changed apparently. The SSIS team caught up with the rest of the BI tools: SSIS projects are now available from the Visual Studio market place.

Read on to see what this means for SQL Server Data Tools.

Comments closed

Deploying SSIS Packages with Powershell

Published 2019-05-03 by Kevin Feasel

Aaron Nelson shows us how we can deploy an Integration Services ISPAC into the SSIS catalog with Powershell:

In my last post, I showed how you can use the SSIS PowerShell Provider to execute an SSIS package with PowerShell.  Of course, in order to execute that SSIS package, it has to get deployed first.  In Part 5 of Andy Leonard’s “SSIS, Docker, and Windows Containers” series he used some PowerShell code from Matt Masson’s blog post to deploy an .ISPAC file to the SSIS catalog.

Click through for the code.

Comments closed

Figuring Out SSIS Memory Requirements

Published 2019-05-03 by Kevin Feasel

Tim Mitchell tries to give us a better answer for SSIS memory requirements than “all of it and then some”:

When planning for memory needs, it is critical to understand how SQL Server Integration Services uses memory. SSIS will allocate memory from the unallocated system memory for each package executed, and surrenders that memory shortly after the package completes its execution. The memory allocated for SSIS package executions runs in the SSIS execution runtime process (ISServerExec.exe, if you are executing the package from the SSIS catalog).

Here’s where the package design has a significant impact on memory use. If a package uses an SSIS data flow, all of the data passing through that data flow is written to memory used by SSIS. For example, consider a package that loads 10 million rows from a flat file to a table. In this case, all 10 million rows will pass through the SSIS memory space during package execution.

Read on as Tim goes into good detail on the topic.

Comments closed

Temporary Staging with SSIS

Published 2019-04-22 by Kevin Feasel

Andy Leonard shares one technique for reusing a data set in SSIS:

A work table is a table defined in a nearby data location; either a schema in the source or target database or in a database on the same instance. I take a constraint-driven approach to work table location selection. Closer – a schema in the same database – is often better for performance.

I write this knowing some folks will frown at the suggestion of polluting a data source or target database with additional schemas and tables. Best practices exist for a reason. It’s helpful to maintain a list of best practices and to include in this list the reasons each practice exists. This could be a case where violating one or more best practices is justified.

Andy throws out a few ideas as alternatives but states his preference for using work tables to solve this problem.

Comments closed

Deploying and Executing Containerized Packages

Published 2019-04-08 by Kevin Feasel

Andy Leonard continues a series on Integration Services in Docker. Part 5 shows how you can deploy a package to a containerized SSIS instance:

Returning to Matt Masson’s PowerShell script – combined with the docker volume added earlier – I have a means to deploy an SSIS Project to the SSIS Catalog in the container.

Part 6 shows how we can run those packages:

An aside regarding attempting SSIS package execution from SSMS connected to an instance of SQL Server in a container (using the runas /netonly trick shared earlier: It appears to work, but doesn’t. The package execution is created but “hangs” in Pending Execution status:

Read both to learn more about Andy’s travails in getting this working.

Comments closed