Solved

SSIS 2008 - When running debug errors

Posted on 2014-04-24
9
306 Views
Last Modified: 2016-02-10
Hi,

Using Execute SQL Task.

When I do the:
Edit Breakpoints
Set the Breakpoints
F5
I am getting a error on a parameter that I am NOT mapping.
That is FILE_DT

the error is:
Package Vaildation Error
   Error at Execute SQL Task: Failed to lock variable "User::FILE_DT"

But the problem is that I don't have a FILE_DT under the Parameter Mapping or the Result Set.

I did create FILE_DT, but I did a remove from the Parameter Mapping Tab.

Please help and thanks
0
Comment
Question by:Amour22015
  • 5
  • 4
9 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40021452
The error message above, means that there is a reference in a component, container, script, etc. to a variable that hasn't been created or has been deleted but the reference is still there.
If you have checked all components and containers and are sure that you haven't left any such reference, another possibility is that you've enabled checkpoints for your package.

When checkpoints are enable, details about the variables are written to the checkpoint file when the package is executed. If you delete any of the existing variables afterwards, make  sure to delete the checkpoint file before restarting the package, otherwise if the checkpoint file is read upon execution, it will try to assign a value to the variable that has been deleted and you'll get this error.
0
 

Author Comment

by:Amour22015
ID: 40022482
Ok,

I am very new to all this.
Looks like I don't understand your comments.

When I do the:
Edit Breakpoints - I right click on the Execute SQL Task: Get File Id From Database_Table
Set the Breakpoints
<F5>
I am getting a error on a parameter that I am NOT mapping. See attached snapshots
That is FILE_DT
I am only referencing FILEID

Please help..
SnapshotPassingVariables.docx
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40022493
Go to the properties of the "Package", right at the top there is a section for checkpoint attributes. Make sure CheckpointUsage is set to nver and SaveCheckpoints is set to False. Save the package and try again.
0
 

Author Comment

by:Amour22015
ID: 40022528
They are currently set to what you mentioned..

I did create a variable with the name:
FILE_DT
but I did a remove from within parameter mapping tab

I have noticed that any variables that I did create and removed are still showing up in the dropdown.  
Could this be the problem? If so then how can you permently delete the unwanted variables from the dropdown? That were created by mistake...

Thanks
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40022554
Oh that's easy, just go to the variable window ( if it's not open go to the menu bar, View>Other Windows>Variable), where you can see all variables and edit or delete them if needed.
0
 

Author Comment

by:Amour22015
ID: 40022593
I did:

View>Other Windows>Variables

Also

Right click within the Control Flow and select Variables

But when the Variables window appeared
there was only
FILEID
FILEIDS

No display of:
FILE_DT

Please help and thanks
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 250 total points
ID: 40022628
It If the Checkpoint is disabled then the reference to the non-existent variable must be in one of the remaining tasks or components.  Try eliminating the suspects by disabling all the tasks and containers first and then re-enable them one-by-one and run the package.

Also it may help getting rid of all potential remnants of the previous changes if you remove all the breakpoints (you can add them again later when this issue is resolved), save the package, rebuild it and try running it.
0
 

Author Comment

by:Amour22015
ID: 40022719
So I found the problem,

I did not know that when doing a parameter check that it looks though all the task within the package.

I thought that when you did a right click on one task and selected "Edit BreakPoints" that it just checked that one task.

Thanks
0
 

Author Closing Comment

by:Amour22015
ID: 40022720
Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now