Solved

SSIS 2008 - When running debug errors

Posted on 2014-04-24
9
307 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

864 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