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?
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.
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.
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.
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.