SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Resolution for SSIS Package Catastrophic Failure

By Nitesh R Dedhia,

Recently we were working on a large project involving many developers and testers working on SSIS packages. Recently one of the package started displaying strange behaviour. The package was checked-into TFS after testing it thoroughly, however, whenever any developer would try to open this package in his/her development environment (off-course after taking latest version of the code from TFS), all would get this error:

Once developer would click the “OK” button, the package would open but without any control visible in the BIDS environment. However if they try to execute this package, it would show success, not showing any green, yellow, or red colours for any control. If any tester would run these packages through the SQL Agent Scheduler or any other tool, he/she will not receive any error.

The problems start when developers try to modify the package or want to analyse the logic written for the package. In these cases they cannot see any controls, and they get the error shown in the image above. However, I can see the different controls in the Package Explorer, but nothing in the Control Flow or Data Flow.

Solution

Whenever a package is created in BIDS, the layout is created and stored inside a tag called <DTS:PackageVariable>. When we modify a package programmatically this code is not updated since it is BIDS generated code. When we open a package in BIDS that has existing layout code, then BIDS does not parse the rest of package to check for any missing objects.

This problem does not occur on packages that are created programmatically since when such packages are opened in BIDS it is able to detect that the layout is not present. Thus BIDS will generate the layout for the first time when the package is opened by performing an auto-layout.

Thus to solve our problem we have to delete all tags labeled <DTS:PackageVariable> from the XML file and then open the package in BIDS. Kindly note there will be more than one tag, and you will need to delete all of them or BIDS will not generate layout code for the package.

Hope this helps you in solving such trouble that you might encounter in your SSIS - BI projects.

 
Total article views: 2798 | Views in the last 30 days: 1
 
Related Articles
FORUM

Save Package Layout?

Is it possible to save package layout?

BLOG

Control Flow Package Parts in SSIS 2016

Control Flow Package Parts are a new feature in Microsoft SQL Server 2016 that attempts to enable co...

FORUM

Controlling Package execution using script task

Controlling Package execution using script task

ARTICLE

SQL Source Control - Q & A

After a webinar for SQL Source Control, there were a large number of questions left unanswered. All ...

BLOG

Security Awareness for Developers - What would you include?

If you were putting together a security awareness training program for developers, what aspects of ...

Tags
 
Contribute