none
ADO.NET Host File Adapter Caches MetaData Assembly in SSIS

    Pregunta

  • Hi All,

    I am using HIS to create a metadata assembly using cobol copy books which I am then using in conjunction with the ADO.NET MsHostFile Adapter is SSIS.  My problem comes when I make changes to and redeploy the meta data assembly.

    eg. If I Add a column to the schema and to the table based on that schema and redeploy the dll.  When I then refresh the connection in SSIS, the adapter does not see the changes to the assembly.  

    The only way I have got around this is to completely move the location of the DLL and point the adapter at the new location.  This is obviously not a workable solution for a production environment.  

    Has anyone experienced/ overcome this issue??  Is there another solution to this?  ie. C# in SSIS Script component passing the columns to the output buffer??

    Barry

    martes, 15 de noviembre de 2011 15:34

Respuestas

  • Hi Barry,

    Have you already tried the following to clear all caches ?

    1. IISReset  (if IIS is installed, IIS is often used by other Microsoft Products)
    2. Restart SQL Server Service (services.msc)
    3. gacutil /i yourassembly /f   => /f force !!!

    Best Regards,

    Steve


    Steve Melan - BCEE My Blog : http://stevemelan.wordpress.com
    miércoles, 21 de diciembre de 2011 19:15

Todas las respuestas

  • When you say you 'refresh the connection in SSIS', where exactly are you doing this at? In BIDS? If so, this could be because BIDS has the information cached itself.
    Charles Ezzell - MSFT
    viernes, 18 de noviembre de 2011 13:31
    Propietario
  • Hi Charles,

    That's correct in the advanced editor of the ADO.NET adapter in BIDS there is an option to refresh the connection.  I have also tried removing the connection and connection manager and re adding them but this doesn't seem to work either, the only way I have been able to work around it is by renaming the dll.

     

    Do you have any idea where BIDS would be caching the connection and how to stop this??

     

    Barry

    viernes, 18 de noviembre de 2011 13:41
  • When you load an SSIS project, the adapter loads itself into memory. With the host file adapter, it will also read in the metadata assembly from the GAC. The refresh button you reference does not refresh the adapter, it refreshes available adapters. When you change the metadata assembly, the adapter will not reload the assembly from the GAC. To have it reread the changed assembly, you need to close the project out, and reload it, so the host file adapter can re-read the assembly. It would be best to have your assembly project and SSIS project in separate solutions, and not have both loaded at the same time to prevent conflicts similar to this. This is no different in reality than when using a linked server. Once you run a query across a linked server, that provider it is loaded into memory until you either stop SQL Server, or remove the linked server itself. If you were using the host file adapter in a linked server, this is what you would have to do to reload the assembly.
    Charles Ezzell - MSFT
    lunes, 21 de noviembre de 2011 11:40
    Propietario
  • Hi Charles,

     

    Thanks for the tip.  I have tried closing and reopening the solution however, I am still receiving the same error.

    This is my metadata assembly;

    and this is the error I receive from SSIS:

     

    "Error 1 Validation error. Account Relationship: Account Relationship:

    An error occurred executing the provided SQL command: "SELECT *   FROM BCAccountRelationship".

    The host file alias 'BCAccountRelationship' could not be found in the metadata assembly.   International.ProcessGatewayExtracts.dtsx 0 0"

    However as you see it is in the assembly.  Am I missing something??
    Barry

     

    lunes, 21 de noviembre de 2011 15:35
  • are there any sql processes running that may have loaded the addembly into memory?


    Charles Ezzell - MSFT
    lunes, 21 de noviembre de 2011 17:52
    Propietario
  • Hi Charles,

    No nothing else other than the metadata assembly solution itself will be using it as this is a new process we are developing.  I have tried closing both solutions and registering the dll in the GAC but still no joy.

    Barry

    lunes, 21 de noviembre de 2011 21:30
  • Hi Barry,

    Have you already tried the following to clear all caches ?

    1. IISReset  (if IIS is installed, IIS is often used by other Microsoft Products)
    2. Restart SQL Server Service (services.msc)
    3. gacutil /i yourassembly /f   => /f force !!!

    Best Regards,

    Steve


    Steve Melan - BCEE My Blog : http://stevemelan.wordpress.com
    miércoles, 21 de diciembre de 2011 19:15