locked
Issue with SSIS/BIDS Setup RRS feed

  • Question

  • Hi there,

    I'm running into a strange string of issues regarding SSIS. I have a bunch of packages designed on a Vista 32 bit machine that work fine on a whole bunch of developers workstations. These package use XML tasks for which the XMLSchemaDefinition variable is defined using a user variable that is stored in my package configuration file which is itself referred in an environment variable. All my connection strings are stored in my configuration and my package is stored as DontSaveSensitive for its protection level.

    Today we were testing these packages on a couple of SQL 64 bit instances. They have SQL 2005, SSIS with SP3 applied. Now, on one of the servers, one package just cannot be opened, it claims it is corrupted (the one with the XML task referred to above). The other packages had to be manually opened and saved (without any other change than just moving an annotation to force a change) before they would work using SQL Agent. In trying to investigate these issues, I realized that on that server, when I open BIDS to load the package, all my data flow tasks have an empty 'properties' section. I'm assuming this is why the component cannot load my expressions for my XMLSchemaDefinition and my other connection string informations.... And in a script task, clicking the input/output section just closes the properties form of the script task...

    It seems to be due to the fact that BIDS is installed, along with a few KB articles (SP1 and another one which ID I forgot but could get back if needed). We are in the process of setting up the client tools again on that one machine to see if that helps but it's as if installing the client tools overwrites some files/assemblies that will be used by SQL Agent when running the package, which causes a number of errors (before I manually opened/saved each package, I would consistently get OLEDB errors regarding acquiring a connection which would test fine when run inside BIDS).

    I'm just wondering if any of those issues have been happening to anybody. I'm just a bit worried because ultimately, this is out of my control regarding how the package is setup and I don't really know how to test this to ensure it is working. As far as we can tell the two servers were setup in a similar fashion. Both are 64bit SQL machines too.

    Anyways, any help would be greatly appreciated.

    Thanks,

    Greg
    Tuesday, April 28, 2009 1:52 AM

Answers

  • Does that particular package (try the original one) work on the other server? When you say the other packages need to be manually open and saved before work on that problem server, it sounds like there is some package version changes involved. It is possible your BIDS and sql agent are not sharing the same version of assemblies. You may try reinstall sql server to solve the problem.
    • Marked as answer by Bob Bojanic Thursday, July 9, 2009 5:41 PM
    Thursday, July 2, 2009 5:55 PM

All replies

  • Do u have a script task or script component in  ur package?
    If yes set the Precompiled option to TRUE.
    Also add "Option Explicit OFF" on the 1st line of the Scripts.
    Hope this helps !! - Sudeep
    Tuesday, April 28, 2009 2:09 AM
  • Hi,

    Not all my packages have script components and the one with my XML task issue does not either. I'm pretty sure this is a general setup issue because if I create an empty project and drag a data flow task with nothing else, its properties are still empty...

    thanks for the reply though,

    Greg
    Tuesday, April 28, 2009 10:29 AM
  • Does that particular package (try the original one) work on the other server? When you say the other packages need to be manually open and saved before work on that problem server, it sounds like there is some package version changes involved. It is possible your BIDS and sql agent are not sharing the same version of assemblies. You may try reinstall sql server to solve the problem.
    • Marked as answer by Bob Bojanic Thursday, July 9, 2009 5:41 PM
    Thursday, July 2, 2009 5:55 PM