14
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 "Test Connection" task, the "foreach" container and the package itself.
I ended up doing something different though. And Jamie Thomson's Verify A Connection ...
13
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 table in SSISDB will be updated. And in order to view the execution report of this run, we need to run something like below:
EXEC SSISDB.catalog.grant_permission ...
12
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.
This is not obvious, as using SSMS to view the job step doesn't display this switch anywhere. The only way that I have found to fix this is to script out a drop ...
10
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 clearly more power than a support person should have.
There are two schemas, internal and catalog. Catalog is meant for us, end users to interact with the SSISDB ...
10
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 on.
Once I had turned MSDTC on I received a new error:
Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: ...
9
I think you're running into a limitation of the UI/debugger.
I created two packages: MakeAllTheFiles and ReadAllTheFiles
MakeAllTheFiles accepts as input the number of files to be created. It will make use of pseudo-random function to distribute the data across a number (7) of sub folder.
MakeAllTheFiles
public void Main()
{
int ...
9
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 AdventureWorks sample database:
SELECT
HAS_PERMS_BY_NAME
(
N'Production.Product',
N'OBJECT',
N'SELECT'
);
Related: To ...
9
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 CAST your source columns to NVARCHAR in the initial SELECT from the database, or use a Data Conversion task to convert them to Unicode strings.
An alternative ...
8
There's nothing I've encountered that provides out of the box capability for this and I'll be delighted if someone proves me wrong.
Therefore, what I do is a controlled failure.
My lookup against DimEmployee should always yield a match. Data was loaded to that table before this dependent package executed. There's no opportunity for source data to change ...
7
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
END
ELSE
SELECT 'FALSE' As Result
You can of course do several things with this, one of the easiest and clearest for SSIS developers would be ...
5
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 that you don't want other people to see ?
SQL Server 2012* does not have any native tools to mask or scrub sensitive data.
In my company, we have developed in-...
5
First of all, you have an additional parenthesis before : character, expression is:
FINDSTRING(
SecurityDescription,"\n",1) > 0
? SUBSTRING(SecurityDescription, 1,
FINDSTRING(SecurityDescription,"\n",1) - 1)
: SecurityDescription
Lineage ID Error
"Lineage ID is a property of the component or transformation used in the data flow task. ...
5
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 should, and you should probably switch to the more recent SQL Native client provider.
To illustrate this point, I created an SSIS package with OLE DB ...
4
SSIS runs outside of SQL Server's memory space so it's going to be contending with the memory allocated to the OS.
How much memory you allocate to SSIS is entirely dependent on how you define your SSIS packages. To go fast, which is what SSIS is designed for, it's going to push as much data into memory so that operations can be performed on it and slap it ...
4
I believe I figured this out, but if anyone has better documentation or expertise, please do post a separate answer, and I will vote it up and accept it.
What I have discovered is that Oracle PL/SQL has a means for expressing the concepts of "byte semantics" and "character semantics" in PL/SQL when declaring character data types.
See the ...
4
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, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated.
An operation which is non-blocking can have the ...
4
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 read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page.
When you try ...
4
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)
+ RIGHT("0" + (DT_WSTR,2)DAY(@[System::StartTime]), 2)
Reading that, I use the Year function to extract the 4 digit year and then cast that as a unicode string.
...
4
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
InitialCatalog - MyDB
UserName - MyUser
Password - ***********
ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;...
3
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 SSISDB. Validated/Verified in SQL 2012/2014
3
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 clause:
--WHERE opmsg.[operation_id] in (SELECT [id] FROM [internal].
--[current_user_readable_operations]) OR (IS_MEMBER('ssis_admin') = 1) OR
--(...
3
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 columns.
Wrap that into another pair of brackets.
Recompose the calling of your SP from
exec p_MySPWithTempTables ?, ?
into
exec p_MySPWithTempTables ?, ? with ...
3
You'll need to configure SSIS to run in 32-bit runtime as Excel does not support 64-bit
Go to the Property page for the Solution, select Debugging and change Run64BitRuntime to False
Running 32-bit SSIS in a 64-bit Environment
3
Without some detail of error messages it is hard to know for sure what's going wrong, however it sounds like it may well be either a permissions issue or a problem running the script on that host.
When an agent job runs, it will run under the agent service account or the credential used to create the proxy that the step runs as.
This means that the account ...
3
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 this references are lost and you need to reset them in the SSIS Catalog.
In SSMS > Connect the instance > open tree Integration Services Catalogs > SSIDB > ...
3
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.blog/2017/07/deploying-ssis-projects-to-a-restored-ssis-catalog-ssisdb/
The following microsoft documentation was also a good second source of information, as ...
3
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 the DataFlow task add an OLEDB Source that Read from Orders Table (the same command used in the Execute SQL Task (first step in your question)
Select * FROM ...
2
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 an error up to the client (in this case, SQL Agent and its log) you would have to avoid catching the error somewhere. I suspect you might have an error handler ...
2
There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally ...
2
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 matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, ...
Only top voted, non community-wiki answers of a minimum length are eligible