Simultaneous Lookups using Full Cache to the same table

Creating multiple simultaneous Lookups to the same table that use Full cache.

 
Whilst developing a DTSX package for my ETL process I came across an issue that had me stumped for a while. If you have multiple Lookup Data Flow items in your packge that perform lookups to the same table then you may encounter a situation where the second lookup to a table generates an error in the package.
 
cc1
 
In the package (above) my calls to to the Lookup table (Segment 2(2) & Segment 3(2)) are made simultaneously and using identical code, however the second request fails with the following error;
 
 cc2
 
A quick bit of research indicated that caching may have been the issue here – https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4010917&SiteID=17 – but I have found an alternative solution to the problem that allows you to keep your caching set to Full. If you change your Lookup Data Flow item such that the lookup columns are defined in a SQL Query and you include a unique alias for the tablename, you can then lookup values in the same table multiple times in the same package using Full cache mode.

 
 

dtproj File Error

"There was an error generating the XML document."
 
I have been developing a number of items in a multi-project solution within BIDS (Business Intelligence Developer Studio) and then the other day, after adding a new DTSX package to my SSIS project, I started to get an error saving the project file (dtproj). All the Visual Studio message box would tell me is ‘There was an error generating the XML document.’ and then I was presented with the ‘Save File As’ dialog box. To fix the problem I tried…
  • Overwriting the dtproj file when the ‘Save File As’ dialog box was displayed.
  • Checking the dtproj file back in SourceSafe.
  • Closing the solution, without saving the changes to the project, re-opening the solution, re-adding the new DTSX file and saving the project file changes.
  • Getting an earlier version of the dtproj file from SourceSafe and re-insterting the existing items to the project and saving the project file changes.

All of the above attempts proved to be in vain as I was just presented with the same message box. After checking my dtproj file differences within SourceSafe it occurred to me that I should edit the dtproj file within Notepad and see if that resolved the problem. I added a couple of new entries into the dtproj file for the new items that were included in my SSIS project and saved the changes. When I clicked back to my open project, Visual Studio recognised that changes had been made to the dtproj file and asked me if I wished to reload the file? I clicked ‘Yes’, the project file reloaded successfully and have been able to continue working with my SSIS project without further error messages.

Error generated loading DTSX file into IDE

"Unable to instantiate XML DOM document, please verify that MSXML binaries are properly installed and registered."

 
I have recently been developing some DTSX packages on my laptop within my VS 2005 IDE and needed to continue the work on a remote machine the following day. Having checked my files into the source control and got the latest version on the remote machine I attempted to open the same packages from within the VS 2005 IDE on the remote machine. The same packages that I had completed the previous day on my laptop were refusing to open cleanly, presenting me with a numbr of error messages and had lost the Data Flow items in my package.
 
Knowing that I had my files under source control, and stored on my laptop, I knew I could retrieve the files again without too much hassle but I needed to find out why they were not loading successfully in this particular IDE. A quick search of the digital cloud provided this little nugget of information http://bisqlserver.blogspot.com/2008/05/ssis-error-opening-packages-msxml.html. Having applied the suggested solution on the remote machine I was able to open my packages without any errors or loss of content.

Package Errors

Errors when loading my .dtsx file?
 
I have been generating an ETL process recently within Visual Studio that required the Connection Manager to store a link to a SQL Server database located on  remote machine. The security setup for this database meant that I was accessing it using the SQL Server security (username / password). My problem occured each time I loaded my solution into Visual Studio as I would get the following error message;
 
Error 1 Error loading Dimensions.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.    
As well as the error message my Data Flow Task items wouldall appear with the yellow warning icon in the bottom right corner indicating that there is a problem with the task item. Fortunately I was able to find a KB article on this one to help resolve my problem – http://support.microsoft.com/kb/918760  – I chose Method 3 and now each time I open my solution file I just have to provide my password and the dtsx file opens without any errors.
 
To get rid of the warning icons in your Data Flow Tasks, on the Control Flow tab, you can either edit the existing Data Flow task or just disable and then enable it again. Providing there aren’t any other errors in your Control Flow item the warning icon will disappear.