Executing, Scheduling and Configuring SSIS packages Webinar Q&A

By: Koen Verbeeck | Updated: 2017-05-12 | Comments | Related: More > Integration Services Best Practices

Problem

A little while back a webinar was presented about Integration Services packages and projects. The main topic was how you can configure parameters and environments, and how you can schedule or execute your packages. This tip answers some of the questions raised by the audience of the webinar. You can view the webinar on demand here.

Solution

Here are the questions.

Question 1 - Read File from UNC Path in SSIS

I created an SSIS package that reads a flat file from a UNC path into a staging table in a database. When I run the package in Visual Studio 2015 it executes no problem. When I deploy it to Integration services and run it within Microsoft Management Studio manually I get a permission error reading the file at the UNC path. If I schedule it as a SQL Agent job there is no flat file permission error.

Answer

Issues like this one typically arise due to permission problems: the user executing the package doesn’t have read/write permissions on the UNC path (or its parent folder). The main problem to figure out is who exactly is executing the package. Visual Studio runs the package using your credentials. Within SQL Server Agent, the package is executed using either the SQL Server Agent service account or with a proxy account if configured to use one.

Normally, SQL Server Management Studio also executes a package using your credentials. You can find this information in the SSIS catalog logging in the Overview report:

SSIS catalog logging - Description: SSIS catalog logging

Since Visual Studio raises no errors, but the execution fails in SSMS, this is something that requires further investigation. Perhaps Visual Studio was run using elevated privileges (as Administrator) and SSMS not.

Question 2 - SSIS Custom Logging Levels

Are customs logging levels IS specific or do they affect all logging?

Answer

They are specific to the Integration Catalog. They don’t have any effect on SQL Server error logging or the transaction log.

Question 3 - SSIS Custom Logging Levels Definition

How do you create custom logging definition?

Answer

You can read all about it in the tip Integration Services Logging Levels in SQL Server 2016.

Question 4 - Run SSIS in Dev, Test and Prod Environments

If you make multiple Environments (dev/test/prod...) and specify different servers, you are still running the packages on the same machine right, the one you deployed to? Besides SQL queries which will run on the target machine. The question behind it is; I would be interested in how you would setup this dev-test-prod line centrally while ensuring resource separation such that for instance dev problems cannot impact production.

Answer

Yes, the packages are still run on the same machine. The data may be read or written at other servers – or T-SQL might be executed on another server – but all data will be transferred to the machine where the SSIS Catalog resides. This means all data will be sent over the network and be pulled into memory (if you are using data flows) of the executing machine. You need to consider if this kind of network traffic is something your environment can deal with.

If you have multiple environments in your SSIS catalog – which should be no issue if you configure your parameters and connection strings correctly – resources will be shared though. The network interface and RAM memory will be shared between different package executions.

Question 5 - SSIS Parameterize Options

I recently took over a project from someone else who had used parameterize. Unfortunately, during my development, changes were made to some of the work they had done. I could not find a way to undo or step back from their parameterize process. I wound up having to go into the package XML and manually edit the changes. Is there way other than starting over or editing the package code to undo the parameterize process?

Answer

Unfortunately, this is not possible. The definition of the parameterization is saved in the XML info of the package. It’s the same as with SSIS variables; the only difference is that variables can change value during execution, while parameters can’t. But variables are stored inside the package XML as well. Changing them for all your packages means manually going over all of them. You might want to look at BIML and see if it helps if you generate your packages from metadata. This might save you the manual labor.

Question 6 - SSIS Deployment

What are some important things to be aware of when creating a package to be deployed at a client site, by the client IT team?

Answer

First you need to check the version of SSIS. If you are using SSIS 2012 or 2014 you can only deploy entire projects if you are using the project deployment model (if you are still using the package deployment model, the set-up is the same as in SSIS 2005-2008). Next you need to find out if the client IT team has the necessary permissions to deploy to the SSIS catalog. This practically means they need to be part of the SSIS admin role in the catalog.

Then you need to decide how they will deploy the packages. You can use the wizard by just double clicking on the .ispac file. They would only need to specify the target server and the correct folder in the catalog. Other options might be a T-SQL or PowerShell script or maybe an application written in C#. With those options, you can control everything: which destination server, which folder, parameters that might be set, etc.

Lastly, there needs to be a check if the environments (if any) are configured correctly. Are they linked properly to the project? Are the variables linked to parameters? Are there any parameters this need to be set manually (this should be avoided)? If needed, you can also write a T-SQL (or PowerShell) script that handles all this. There are plenty of stored procedures available in the catalog to create environments, set parameters and so on.

Question 7 - SSIS Project Parameters

If using a project param, can the package still be deployed as a package, and will it then retain the project param when deployed?

Answer

Only with SQL Server 2016. There you have the option to deploy an individual package (as described in the tip SQL Server Integration Services 2016 Incremental Package Deployment). Project parameter configurations are retained (they are deployed alongside the package). In SSIS 2012-2014 you have no choice but to deploy the entire project.