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.
...
- 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 "...
- 1,895
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 ...
- 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 ...
- 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 ...
- 23.7k
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 ...
- 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 ...
- 10.1k
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
...
- 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 ...
- 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 ...
- 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(...
- 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 ...
- 41
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, ...
- 16k
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 ...
- 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)
+ ...
- 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
...
- 71
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 ...
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 ...
- 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 ...
- 149
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....
- 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 ...
- 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 ...
- 16k
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 ...
- 175
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 ...
- 21
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 ...
- 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 ...
- 171
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
- 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.
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 ...
- 413
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( ...
- 3,393
Only top scored, non community-wiki answers of a minimum length are eligible