SQL Server Performance

Job runs on Named Instance using Database on Named Instance executing SSIS pkg on Default Instance

Discussion in 'Getting Started' started by lcerni, Jun 9, 2010.

  1. lcerni New Member

    Background:
    Our SSIS packages have been deployed to the MSDB database of the default instance.
    We have jobs on this default instance that run these SSIS packages.
    I have now installed a named instance. I do not think I can copy the SSIS packages to the named
    instance, can I? I need to use the SSIS packages that have been created on the default instance.
    I want to set up the same jobs that exist on the default instance to the named instance
    using the database on the named instance.
    I don't think that I can copy the packages from the default instance to the named instance.
    Am I not correct?
    Is it possible to have a job that runs on the named instance using a database on the named instance
    execute a package on the default instance? And if so how?
  2. preethi Member

    Where do you store the SSIS Package. I have stored the package in a drive and it is used by both instances. I made a few changes to suit the authentication etc., but most of the part remained unchanged.
    Hope you are using parameters/configuration settings properly.
  3. lcerni New Member

    I know that SSIS can be installed only once and I want it to be on the default instance.
    We have packages on the default instance and a sql agent job runs to execute them using a database
    on the default instance. What I am wondering is if I
    have a named instance on the same server, back up and restore a database from the default instance
    to the named instance can I create jobs on the named instance using the SSIS packages on the
    default instance but using the database on the named instance? Thus I am creating a duplicate
    environment on the named instance. Same jobs, same database (but it is a QA database instead of
    lets say a production database), but the packages are on the default instance. I do have a config
    file. I have tried running the job but it fails and I cannot find any logs to find any error
    messages.
  4. lcerni New Member

    Well we figured out what was going on. It was the permissions on the directory folder. The data was being loaded into the table but the file could not be moved into the archived folder. The SSIS packages and the config files were set up properly after all. I learned alot debugging this issue. I realize that there is alot more for me to learn about SSIS.
  5. satya Moderator

    Appreciate your feedback on the resolution, it helps others too and for such SSIS packages changes between the instances you have to make sure to copy/manage the config file correctly.

Share This Page