15 votes
Accepted

SSIS 2012: The environment reference n is not associated with the project

The reason for this is that the existing job step that executes the package contains a /ENVREFERENCE switch in the command line, and that value must be updated for the new version of the package. ...
user avatar
  • 2,078
14 votes
Accepted

How do I Continue After a Failure in SSIS

To answer the question as I asked it, it is possible to continue after failures by making sure that MaximumErrorCount is zero for the task and its parent containers... In this case that means the "...
user avatar
13 votes
Accepted

Permission to view execution report in SSIS Catalog

Here is our solution (believe me it will work perfectly!) After investigating the procedure about how the execution reports are stored, we found that every time a job runs, the internal.executions ...
user avatar
  • 291
10 votes

Permission to view execution report in SSIS Catalog

With the caveat that I'm not a security person... There is no predefined database role other than ssis_admin that is special to the SSISDB. That allows one to do all the SSIS things but that's ...
user avatar
  • 15.6k
10 votes
Accepted

Problem creating a transaction in an SSIS package

I solved the problem. I had read in several places that DTC needs to be started on the source machine as well as the destination. So in my place my workstation as well as the server the instance is ...
user avatar
9 votes
Accepted

Verify if user has read access to a table

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME*. For example, to show if the current security context has SELECT permission on the Production.Product table in the ...
user avatar
  • 72.1k
9 votes
Accepted

SSIS export to CSV Flat File Destination task, error The code page on input column is ... and is required to be

The issue is caused by a clash in code pages between 1252 (i.e. Windows-1252) in SQL Server and 65001 (i.e. UTF-8) that your CSV file is expecting. I believe one solution to the issue would be to ...
user avatar
7 votes
Accepted

How can I restrict the version of the target DB instance in a SSIS package?

Create an initial Execute SQL Task in the Control Flow and use the following code in it: IF ((SELECT CAST(SERVERPROPERTY('ProductVersion') as CHAR(2))) = 11) BEGIN SELECT 'TRUE' As Result ...
user avatar
  • 2,369
6 votes

SSIS changes SQL Server source data type from date to WSTR

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008. I suspect the provider isn't detecting the data type as it ...
user avatar
  • 15.4k
5 votes
Accepted

Scrubbing sensitive data

Scrubbing sensitive data is a vast topic. You basically need to define as per your company's data protection policy - what is considered PII (Personally Identifiable Information) or what is sensitive ...
user avatar
  • 61.3k
5 votes
Accepted

SSIS 2012 VS_ISBROKEN on Derived Column

First of all, you have an additional parenthesis before : character, expression is: FINDSTRING( SecurityDescription,"\n",1) > 0 ? SUBSTRING(SecurityDescription, 1, FINDSTRING(...
user avatar
  • 1,212
4 votes

SSIS 2012: The environment reference n is not associated with the project

Old post but to help others and might be a solution: When the SSIS Package is executed from a job it's going to use the references set on the SSIS Project catalog. When deploying a SSIS project again ...
user avatar
4 votes

How do I determine if a SSIS transform is non-blocking, partially blocking, or fully blocking?

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers. An operations which is partially-blocking writes data onto new buffers, ...
user avatar
4 votes

Execute T-SQL Only If AlwaysOn Database Is Primary

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to ...
user avatar
  • 41.4k
4 votes
Accepted

How to format SSIS Expression for weekend

Getting a date To get the YYYYMMDD, sometimes written as CCYYMMDD, I would use an expression like (DT_WSTR,4)YEAR(@[System::StartTime]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2) + ...
user avatar
  • 15.6k
4 votes

SSIS package hits "login failed for user" when simultaneous operations occur against the same target database

Here it is: the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments: ServerName - MyServer ...
user avatar
3 votes

Permission to view execution report in SSIS Catalog

Commenting out the WHERE clause in these views from SSISDB: SSISDB.catalog.executions SSISDB.catalog.event_messages SSISDB.Catalog.folders and provide the DB_READER access to the user/group in ...
user avatar
3 votes

Permission to view execution report in SSIS Catalog

Very simply... Comment out the WHERE clause in these two views: SSISDB.catalog.executions SSISDB.catalog.event_messages Done. Change the view catalog.event_messages by commenting out the WHERE ...
user avatar
  • 41
3 votes

Is it possible to use a temp table in a data flow source?

These steps helped me: Write the final result set into a table. Script that table as CREATE into a new New Query Editor Window. Remove everything except the open and close brackets that define the ...
user avatar
3 votes
Accepted

SSISDB master key

I did some reading, and opening the master key was only part of what I had to do. I had to completely configure the new server for SSIS. I found the following blog post helpful, https://andyleonard....
user avatar
  • 666
3 votes
Accepted

Optimize SSIS package for fewer queries

You can achieve this without using Foreach enumerator, just use one data flow task to achieve this. Building the Package First of all, add a Data Flow Task to the control Flow 1. OLEDB Sources In ...
user avatar
  • 1,212
2 votes

SQL Server 2012 Agent Job not providing full failure message for SSIS Packages

SSIS handles errors in two ways. One is raising an error. The other is failing a task/container/package. The two are actually quite separate, and you can easily see one without the other. To bubble ...
user avatar
2 votes
Accepted

SSIS Package truncation and possible data loss (error code: 0xC020901C)

Okay I found the solution after searching and testing many things. The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not ...
user avatar
2 votes

Permission to view execution report in SSIS Catalog

So this is an issues that I have run into over the last few days, and I have to say this post has definitely helped, although none of the answer were exactly what helped. So like this post states, I ...
user avatar
2 votes
Accepted

How to identify where a package execution has spent most of the time?

Method 1 : YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012: '... ' Declare and instantiate objects here. Dim app As New ...
user avatar
  • 6,995
2 votes

SSIS 2012: What rights are needed to connect to SSDB via DMO

The answer is contained in http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx 1.To allow a login to be able to read/execute only one project, but not able to ...
user avatar
2 votes

How can I eliminate false "connection string format is not valid" messages from SSIS log?

If you use the Excel connection manager for example, you should set the ExcelFilePath property as an Expression instead of the ConnectionString
user avatar
  • 21
2 votes
Accepted

VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database?

Looks like I need to be careful to specify my column length in CHAR. For example, Instead of VARCHAR2(255), I need to ensure it is VARCHAR2(255 CHAR). If I do this, SSIS sees it as DT_WSTR.
user avatar
2 votes

Starting SSIS Package through SSIS Catalog

I would agree with the comment from Travis Page. Having just gone through this is sounds like either the Service Principal Names(SPN) are missing for the SQL Service or the file share. Ultimately you ...
user avatar
2 votes
Accepted

SSIS - How to parse extensions from path-filename strings

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator ? Conditional One way to implement the Derived Column would be to parse the string like this TOKENCOUNT( TOKEN( REVERSE( ...
user avatar
  • 3,393

Only top scored, non community-wiki answers of a minimum length are eligible