Execute SQL Task: An error occurred while assigning a value to variable "RecentPackageExecutionDate": "Unable to find column LastTimeJobRunDateandTime in the result set.".

I created a script task in the control flow for getting the recent date of the job from DB. It was working previously when i ran the package but now its throwing an error as mentioned in the title.

Can someone help me on this one ?
Gowtham RamamoorthyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Please attach an image of your SQLStatement as it appears in the SQL Task editor and the Result Set tab or at least double check them to make sure the column name / alias match the Result Name column in the Result Set tab.
Phil DavidsonCommented:
The task may expect a variable to be a data type that is not compatible with the data type of the variable RecentPackageExecutionDate.  If it had been working, sometimes the conversion of a field from one data type to another is ok as long as the data didn't have some unexpected character (e.g., an accent mark or number that was contravariant).  

Can you see if you have anomalous data?

In the target table, can you manually check if the LastTimeJobRunDateandTime column is there?  I would run this:

with(nolock)
select LastTimeJobRunDateandTime from NameOfTable

Open in new window


--replace NameOfTable with the name of the table involved
as long as the table isn't too big, and it isn't a production database

If the ANSI NULLS setting was turned on, null values may not be able to go to the variable.  This change would explain why it used to work and now it doesn't.

So I think there are three likely possibilities: 1) anomalous data that is contravariant to the data type of the variable.  2) a change to the table (the column no longer exists)  3) ANSI NULLS used to be set to off now they are set to on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.