Package in Job failing, No Clear Visiable error, Errors logs clear, Unsure How to Approch for Fix

Question

Package in Job failing, No Clear Visiable error, Errors logs clear, Unsure How to Approch for Fix

quinn.jay

SSCertifiable

Points: 5505
More actions
January 31, 2017 at 11:06 am

#318534

Hello, taken over where a contractor left off. He created a series of multi-step processes broken down to jobs that run packages in a certain order. This process runs once a year, makes changes to changed static dim over the year, and then loads new static dims, reloads dyn dims, geography data, and then after clearing out data from cubes (other stepped processes), and readying a back-loading a several years of data, it all gets reprocessed into the cube new for 2017 year. This is a Tabular data model, and it's using partitions tables. Server Windows 2012R2, SQL Server 2016.
The issue now is a job that has three packages in it, first package ran fine backing up, truncing and loading in new dim values, ran fine, Then it rolled to the second package in the Solution where it failed. Below the screenshots I have more details.
From The error messages here is a screen shot of that error:

Here is a screenshot of the entire package in question, as to the basics of design:
Based on the error message, the first Execute SQL Task in the Container is failing, executing this: SELECT [PartitionName]
FROM [BLAH_Partitions].[dbo].[SSASPartitionDimsStatic] WHERE DatabaseID = ?
Seems that it's not getting what is was expecting from one Execute SQL Task outside the container feeding the Sequence Container. In it is: Select 'DataBaseID' <-- real database name
And it is as if its populated with NULL, based on the above screenshot error.
If I try to step though running the package in VS, It immediately errors on that first outside the loop Execute SQL task, here is that screenshot:
I'm unsure on where or how to edit or what to change to rectify this error and to satisfy getting the correct value.
Please let me know what other details I can track down and share in helping resolve this issue.
Thanks,
JPQ

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

quinn.jay - Tuesday, January 31, 2017 11:06 AM
Hello, taken over where a contractor left off. He created a series of multi-step processes broken down to jobs that run packages in a certain order. This process runs once a year, makes changes to changed static dim over the year, and then loads new static dims, reloads dyn dims, geography data, and then after clearing out data from cubes (other stepped processes), and readying a back-loading a several years of data, it all gets reprocessed into the cube new for 2017 year. This is a Tabular data model, and it's using partitions tables. Server Windows 2012R2, SQL Server 2016.
The issue now is a job that has three packages in it, first package ran fine backing up, truncing and loading in new dim values, ran fine, Then it rolled to the second package in the Solution where it failed. Below the screenshots I have more details.
From The error messages here is a screen shot of that error:

Here is a screenshot of the entire package in question, as to the basics of design:
Based on the error message, the first Execute SQL Task in the Container is failing, executing this: SELECT [PartitionName]
FROM [BLAH_Partitions].[dbo].[SSASPartitionDimsStatic] WHERE DatabaseID = ?
Seems that it's not getting what is was expecting from one Execute SQL Task outside the container feeding the Sequence Container. In it is: Select 'DataBaseID' <-- real database name
And it is as if its populated with NULL, based on the above screenshot error.
If I try to step though running the package in VS, It immediately errors on that first outside the loop Execute SQL task, here is that screenshot:
I'm unsure on where or how to edit or what to change to rectify this error and to satisfy getting the correct value.
Please let me know what other details I can track down and share in helping resolve this issue.
Thanks,
JPQ

What is the Protection Level of the package?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

Phil Parkin - Tuesday, January 31, 2017 11:11 AM
quinn.jay - Tuesday, January 31, 2017 11:06 AM
Hello, taken over where a contractor left off. He created a series of multi-step processes broken down to jobs that run packages in a certain order. This process runs once a year, makes changes to changed static dim over the year, and then loads new static dims, reloads dyn dims, geography data, and then after clearing out data from cubes (other stepped processes), and readying a back-loading a several years of data, it all gets reprocessed into the cube new for 2017 year. This is a Tabular data model, and it's using partitions tables. Server Windows 2012R2, SQL Server 2016.
The issue now is a job that has three packages in it, first package ran fine backing up, truncing and loading in new dim values, ran fine, Then it rolled to the second package in the Solution where it failed. Below the screenshots I have more details.
From The error messages here is a screen shot of that error:

Here is a screenshot of the entire package in question, as to the basics of design:
Based on the error message, the first Execute SQL Task in the Container is failing, executing this: SELECT [PartitionName]
FROM [BLAH_Partitions].[dbo].[SSASPartitionDimsStatic] WHERE DatabaseID = ?
Seems that it's not getting what is was expecting from one Execute SQL Task outside the container feeding the Sequence Container. In it is: Select 'DataBaseID' <-- real database name
And it is as if its populated with NULL, based on the above screenshot error.
If I try to step though running the package in VS, It immediately errors on that first outside the loop Execute SQL task, here is that screenshot:
I'm unsure on where or how to edit or what to change to rectify this error and to satisfy getting the correct value.
Please let me know what other details I can track down and share in helping resolve this issue.
Thanks,
JPQ
What is the Protection Level of the package?

I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

quinn.jay SSCertifiable Points: 5505 More actions · Answer 4

Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?

Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?

quinn.jay SSCertifiable Points: 5505 More actions · Answer 5

quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?

And with that change, I presume I have to save, build and deploy?

quinn.jay SSCertifiable Points: 5505 More actions · Answer 6

quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?

And with that change, I presume I have to save, build and deploy?

quinn.jay - Tuesday, January 31, 2017 11:59 AM
quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?
And with that change, I presume I have to save, build and deploy?

I changed to nonsensitive, on all the packages in the solution, and saved, and when I did a build, I get the error Proejct Consistency check failed, and claims the three packages dont have same protection level yet they do

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

quinn.jay - Tuesday, January 31, 2017 12:06 PM
quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?
And with that change, I presume I have to save, build and deploy?
quinn.jay - Tuesday, January 31, 2017 11:59 AM
quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?
And with that change, I presume I have to save, build and deploy?
I changed to nonsensitive, on all the packages in the solution, and saved, and when I did a build, I get the error Proejct Consistency check failed, and claims the three packages dont have same protection level yet they do

Change the project too. Packages + project need to be in sync.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

quinn.jay SSCertifiable Points: 5505 More actions · Answer 8

Phil Parkin - Tuesday, January 31, 2017 12:11 PM
quinn.jay - Tuesday, January 31, 2017 12:06 PM
quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?
And with that change, I presume I have to save, build and deploy?
quinn.jay - Tuesday, January 31, 2017 11:59 AM
quinn.jay - Tuesday, January 31, 2017 11:59 AM
Phil Parkin - Tuesday, January 31, 2017 11:50 AM
quinn.jay - Tuesday, January 31, 2017 11:44 AM
I try too look for the props in the package and I dont see security to tell you what the protection level of the package is, do you know how I can find out?
Mines is EncryptSensitiveWithUserKey..., I take it should be changed to DontSaveSensitive?
And with that change, I presume I have to save, build and deploy?
I changed to nonsensitive, on all the packages in the solution, and saved, and when I did a build, I get the error Proejct Consistency check failed, and claims the three packages dont have same protection level yet they do
Change the project too. Packages + project need to be in sync.

I ended up doing that too. After much trouble shooting, the package design was bad, but in the source, I ended up upgrading the package version form SQL14 to SQL16,m which helped in better error messages. I was lead to believe this job ran before, but it was such a mess, I dont think it ever ran, not even once.