Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS 2008 - When running debug errors

Posted on 2014-04-24
9
Medium Priority
?
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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
 
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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.

609 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