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
Solved

SSIS 2008 - When running debug errors

Posted on 2014-04-24
9
309 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when creating an UPDATE Trigger in SQL 6 18
SQL Select in Access 2003 3 23
SSRS Deployment problem 5 64
where to find DTS instalation package for sql 2014 64 bit 3 10
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

808 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