September 3, 2010 at 3:38 am
Here's a good one - anyone know why this happened?
1. Create an SSIS2008 package.
2. Import it into MSDB under a sub - folder.
3. Attempt to schedule the task in Agent - select Package Source SQL Server, Server = the default instance; the package is MISSING from the list, even though the folder, and the other packages in the folder, are present.
4. Specify Server = the other (non - default) instance and the package is there, along with the other 2 which showed when the default instance was selected.
There are 2 instances of SQL2008 on the box. I don't know when SSIS was installed - with an instance or separately. Everything has been done with a server admin and SQL sysadmin account.
So, why do some packages appear in both servers' MSDBs and some in only one, and how does SSIS decide which MSDB it'll use?
Thanks for your help - if you can! This is driving me slightly wibble!
September 3, 2010 at 5:19 am
How did you deploy the package to the MSDB folder?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 6, 2010 at 6:30 am
The deployment was using SSMS 2008 on a remote PC. The security setup was (on the development package) the default encrypt sensitive using user key, and on the import to MSDB was set to 'rely on server security' (or whatever the wording is).
September 6, 2010 at 7:00 am
No matter how many of SQL instances are installed on a box there is only one install of SSIS. This is used to service all the SQL installations. I am no expert in SSIS usage but I would have thought you specify which msdb to upload to at the time you do it from SSIS.
you can configure SSIS to look at all your msdbs at once from SSMS - see this blog, it explains it quite well:
Its talks about clusters but it holds tru for multi-instance standalone servers.
The secret is in the editing of the MsDtsSrvr.ini.xml file
---------------------------------------------------------------------
September 7, 2010 at 11:23 am
Since SSIS is server based instead of instance based, there didn't appear to be a good way to look at the instance. I will look into the information that was already given, but I found a way to handle it since I am always only working in one instance.
I found an article in Books On Line about Configuring the Integration Services Service to use a local copy of Sql Server Express and point to an instance on a server.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_4deptrbl/html/36d78393-a54c-44b0-8709-7f003f44c27f.htm
Per that article, I changed my local configuration of SSIS to point at the instance on the server instead of my local instance. When I connect to my local SSIS from Management Studio, I see the SSIS packages that are that instance on the server.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply