FTP Task – FTP Import: Error: Directory is not specified in the file connection manager $Package-variableName

Possibly save 4 hours of your time: I notice some of my SSIS jobs are no longer working and the errors are different for each of them. But one error gave clue about the package variable issue.

When I run the package directly using environment variables, it works fine. But when I use a job with proxy/credential identity, it failed. So there is another hint this is related to permission issue.

What caught me off guard was these jobs used to work.

The jobs ftp files from/to the ftp server to a network location.

The variable in this case was a network path.


We change the environment variable to point to a local path and it works. This identifies that the issue has to do with the identity used to run the job does not have access to the network location defined by the variable.

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)


SSIS – An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

Possibly save 4 hours of your time: When you deploy a package with FTP Connection Manager you can get this error in which it says the password was not allowed.

The SQL Server Agent job is set up to use environment variables and the variables are also setup. However the job still fails when it try to connect to the FTP server. So for some reason the Environment variables are not working. The following environment variables are there (i.e. naming convention may be different from yours)

  1. FTPConnection_ServerName
  2. FTPConnection_ServerPort
  3. FTPConnection_ServerUserName
  4. FTPConnection_ServerPassword

The problem was that these variables are not directly used by the FTP Connection Manager in the package yet. You can right click the FTP Connection Manager and choose Parameterize. By choosing the Use existing parameter, that will link to the package parameter used in this package.

Parameterize right click


P.S. > If you are not using environment variables in your deployment, then you may also want to change protection level by right click Properties on the project and the package to Do not save sensitive data. However this is not recommended. Please use at own risk. There are other scripting solutions so that it is not required to deploy the password. Please google that.

do not save sensitive data

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

SSIS – Project Parameters, Package Parameters, User Variables

Possibly save 1 hour of your time: There are three types of parameters in SSIS that you can configure to use by your packages.

  1. Project parameters
  2. Package parameters
  3. User variables

You can access project parameters from the Team Explorer >> Project.params.

Project Parameters

Next you can access the package variables by double clicking the package to open the designer and choose tab Parameters.

Package Variables

Finally, you also have user variables. You can right click on the Control Flow designer and choose User Variables.

User Variables

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

SSIS – Foreach Loop Container and Execute SQL Task

Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
  • Execute SQL Task – Get the records that need to be processed
  • Foreach Loop Container – Loop through each record
    • Execute SQL Task – Process one record at a time

Create two user variables:

  • i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
  • i.e. CollectionID (Int32) – the current ID to be processed


The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?


You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).


Now you want to store your Result Set into a user variable for looping through later.


In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.


We want to a user variable to store the current processed id.


You can specify a query or stored procedure to process each record.

i.e. [schema].[usp_ProcessRecord] NULL, ?

The above example has two input variables where one is always NULL and the ? represents an input parameter.


In this example, the input is a large integer.



Setup SQL Server Integration Services environment and SQL Agent Job to run deployed SSIS package

Possibly save 8 hours of your time: After you create SSIS package and build the project (bin folder should have .ispac file), the next step is to deploy that to a Microsoft SQL Server instance environment and have a scheduled job to run the package. It is not trivial to setup a proper environment. The steps below are targeting Microsoft SQL Server 2012.


  • Please make sure that you have SSISDB catalog under Integration Services Catalogs already created. If not, you can right click and choose Create Catalog.


  • A specific user or system user permission will be needed to run the job to execute the package. From here on we call this user the system user.
  • If the package requires read and/or write access to specific database, provide the following database role permissions as needed to the system user:
    • db_datareader
    • db_datawriter
    • SSIS
  • If the package requires execute permission for specific schema on a specific database, provide the following permissions as needed to the system user:
    • Execute
    • Alter (if using bulk insert tasks in SSIS)
  • For msdb system database, the system user needs to be added to Security > Users. Under Owned Schemas, check SQLAgentUserRole.
  • For SSISDB database, the system user needs to be added to Security > Users.
  • The database administrator who will be running the command script to create the job to run the package will need sysadmin role.
  • If you want to receive SSIS job notification on success or failure, you will need to setup an account under Management > Database Mail. Then right click SQL Server Agent, click Alert System and Enable mail profile and choose the account profile created.

Script Implementation

  • Please see at the bottom for the script. The scripts can be run in command prompt directly or embed in a .cmd script file first. Any word surround by %% are variables that you can set.
  • cmd Script will create
    • folder in SSISDB
    • Deploy packages via .ispac
    • run the following SQL script
  • SQL Script will create
    • delete previous existing job
    • create Credential
    • create Proxy
    • create Alerts
    • create SQL Agent Job via .sql
    • add package(s) to the job
    • add job schedule


  • Setup environment variables if your packages requires specific configuration for this environment – Integration Services Catalog >> SSISDB >> Project >> Environments
  • Update Project Variable Environment
    • Right click project. Select Configure.
    • Under References, add the environment created previously.
    • Under Parameters, edit the same values as in the environment section to use environment variables.
  • Set project level permissions
    • At the SSISDB folder level, add system identity user account with read, execute objects and read objects permissions
    • At the projects level, add read and execute permission for the system identity user.
  • Setup working folder permissions for system user if using working folder (typically if data files are involved in the package, they need to be somewhere)

Script – create folder in SSISDB

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create folder in SSISDB

echo Creating folder in SSISDB if not exists…
echo %sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”
%sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”

echo *******************************************************

Script – deploy packages via .ispac file

set isdeploymentwizard=”C:\Program Files\Microsoft SQL Server\110\DTS\Binn\isdeploymentwizard.exe”

REM Deploy the ETL
echo ETL Deployment is running…
echo %isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
%isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
echo ETL Deployment Completed
echo *******************************************************

Note: If you get error 0xC0011008 such as “the package failed to load due to error error loading from xml”, ensure you are targeting the right version of database via project properties.

Script – to run the following SQL script in one file with passed in parameters

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create SQL Agent Job
echo Creating SQL Agent Job…
echo %sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
%sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
echo SQL Agent Job has been created

Delete previous existing job

USE [msdb]

–Clean up SQL Agent Job


SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N’Your Job Name’)

IF (@jobId IS NOT NULL)    EXEC msdb.dbo.sp_delete_job @jobId go

Create Credential

–Create Credential Note: change the credential name

use master


IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N’Your Credential Name’) DROP credential [Your Credential Name]


create credential [Your Credential Name] with identity = N’$(identity)’, secret = N’$(password)’go

Create Proxy

–Create Proxy Note: change the proxy name

USE msdb


IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N’Your Proxy Name’) EXEC dbo.sp_delete_proxy @proxy_name = N’Your Proxy Name’


EXEC msdb.dbo.sp_add_proxy @proxy_name=N’Your Proxy Name’,@credential_name=N’Your Credential Name’, @enabled=1


EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Your Proxy Name’, @subsystem_id=11GOEXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Your Proxy Name’, @login_name=N’$(identity)’


Create Alerts

–Create Operator

USE [msdb]


IF EXISTS(SELECT 1 FROM msdb.dbo.sysoperators WHERE name = N’Your Operator Name’) EXEC sp_delete_operator @name=N’Your Operator Name’


EXEC msdb.dbo.sp_add_operator @name=N’Your Operator Name’,  @enabled=1,  @weekday_pager_start_time=90000,  @weekday_pager_end_time=180000,  @saturday_pager_start_time=90000,  @saturday_pager_end_time=180000,  @sunday_pager_start_time=90000,  @sunday_pager_end_time=180000,  @pager_days=0,  @email_address=N’$(operator)’,  @category_name=N'[Uncategorized]’


Create SQL Agent Job


–Create SQLAgent Job

USE [msdb]



SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)


EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Your Job Name’,  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=2,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N’No description available.’,  @category_name=N'[Uncategorized (Local)]’,  @owner_login_name=N’$(identity)’,  @notify_email_operator_name=N’Your Job Name’, @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add package to the job

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Your Step Name’,
@os_run_priority=0, @subsystem=N’SSIS’,
@command=N’/ISSERVER “\”\SSISDB\Your Folder Name\Your Project Name\Your Package Name.dtsx\”” /SERVER “\”$(serverName)\”” /ENVREFERENCE $(environmentId) /Par “\”$ServerOption::LOGGING_LEVEL(Int16)\””;1 /Par “\”$ServerOption::SYNCHRONIZED(Boolean)\””;True /CALLERINFO SQLAGENT /REPORTING E’,
@proxy_name=N’Your Proxy Name’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Note: You can repeat this to add more packages as steps in your job.

Add Job Schedule
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Your Schedule Name’,  @enabled=1,  @freq_type=4,  @freq_interval=1,  @freq_subday_type=8,  @freq_subday_interval=12,  @freq_relative_interval=0,  @freq_recurrence_factor=0,  @active_start_date=20160616,  @active_end_date=99991231,  @active_start_time=0,  @active_end_time=235959,  @schedule_uid=N’d5de52a3-a8e8-49ca-92d0-fbbc3ee10098′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’$(serverName)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


GOTO EndSave






Download SQL Server Data Tools (SSDT) 17.1 for Visual Studio 2015 (SQL Server Integration Services – SSIS – project template)

Possibly save 1 hour of your time: This is a quick reference guide for getting the SSIS project template within Visual Studio 2015.

Download link here https://go.microsoft.com/fwlink/?linkid=849393

Source: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Once you download you can just double click to run the installation. You can check the following features that you need. I need SSIS so I will check SQL Server Integration Services.

install SSDT vs2015

Then you will have to agree to license terms.

If you select all features, it will take about 1 hour to install.

Once install is completed, open Visual Studio 2015, and go to File > New Project, you should be able to select the following Business Intelligence > Integration Services > Integration Services Project template.

ssis template vs2015



Trigger and execute SQL Server Agent Job

Possibly save 2 hours of your time:

Recently, I was asked to find a generic way for non-technical administrators to be able to trigger or execute SQL Server Agent Job on their own on demand.

One solution is to build a windows authenticate administration website within our network domain. This web application will use windows authentication authenticate against specific windows user/group that has permission to trigger/execute a back end job.

public Exception ExecuteSqlAgentJob(string connectionString, string jobName)
Exception exception = null;

var dbConn = new SqlConnection(connectionString);
var execJob = new SqlCommand();
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = “msdb.dbo.sp_start_job”;
execJob.Parameters.AddWithValue(“@job_name”, jobName);
execJob.Connection = dbConn;

using (dbConn)
using (execJob)
catch (Exception ex)
exception = ex;

return exception;

The application pool identity that runs this application should have access to:

  • the database which the SQL job targets
    • Go to specific database >> Security >> Users >> add login
  • msdb database
    • Go to msdb database >> Security >> Users >> add login
    • And give it SQLAgentOperatorRole membership
    • This gives permission to execute dbo.sp_start_job
    • Note: Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.