Post reply

Problem with Schemas and integrations Services

  • December 13, 2006 at 8:27 am

    #115526

    I am using Integration services to populate data from various regions (ie Europe, NorthAmerica and Asia). The data structure is the same yet it is required that the data be loaded into seperate tables.

    To keep to a single code base to load the regions I have designed the DTS packages to load the tables into under seperate schemas named after the region.

    For example if we take the Customer table for then Europe reiogn this would be Europe.Customer and for Asia it would be Asia.Customer

    Using a different user for each region I can grant rights to the specific schema and deny rights to all other schemas. This enables me just to change the connection string in the package and the appropriate schema is used running the same code.

    The problem is that SQL Server appears to get confused. If I run the Europe region this works fine. If I then run the NorthAmeria region the package fails with a permission denied error on the NorthAmerica. If I restart SQL Server and rerun the NorthAmerica region the packages works fine. The problem also happens if I run the regions in reverse order with a permissoin denied on the Europe region. The interesting thing is that if restart SQL in between running the packages they run perfectly.

    Sorry if this is a bit vague but I am stumped on where to go with this.

    Any ideas would be much appriciated.

    Thanks

    Daniel

  • December 13, 2006 at 11:30 am

    #677872

    I don't know what cuasing this issue but you can load into a permanent temp table and then copy to the respective tables...

    Europe---->Perm.TempTable----->Europe.table.

    Asia----->Perm.TempTable---->Asia.table

    Who is executing the package?

    MohammedU
    Microsoft SQL Server MVP

  • December 14, 2006 at 3:10 am

    #678015

    Thanks for you idea which I will try. I am hoping not to have to change the packages because I have quite a few.

    I am executing the package on the server logged in as an adminstrator.

    The connections within the packages are using a SQL user account within the connections strings.

    Thanks

  • December 14, 2006 at 11:29 am

    #678163

    Did you BULK_INSERT/Linked server queries to load the data... you can totally aoid SSIS package...

    Are you loading from text files are from sql tables?

    MohammedU
    Microsoft SQL Server MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply