Pick out the SSIS package from a job

Question

Hi Everyone,

I have a SQL 2k5 instance. Previously someone embedded a SSIS package in a job and I just want to look into the SSIS package to understand the logics. However, in the job property, the package source is "SQL Server" and I don't know where I can find the package itself(actually I tried open corresponding integration service but found nothing) and make a copy. Could someone give me some advice? Thank you very much.

regards,

Oliver

Answer 1

Hi Oliver,

this actually depends on the store that was selected for deplying the server. If you are at this point here

http://msdn.microsoft.com/en-us/library/cc280561.aspx

As you are using SQL 2k5 there is no SSIS package store, it is either File System (where you easily could download the file from= or SQL Server. If it is SQL Server, you can connect to it using the SQL Server name in the UI mentioned in the article.

The connection can be easily done using the instruction from this article:

http://msdn.microsoft.com/en-us/library/aa337083(v=sql.90).aspx

-Jens

---

Jens K. Suessmeyer
http://blogs.msdn.com/Jenss

Answer 2

Hi Jens,

Thanks for your reply. Actually 2 of the job parameters are as below:

Package Source: SQL Server

Server: DE-S-0157590\DE_S_0157590

I tried to connect to integration service "DE-S-0157590", and I expanded every folder in the object panel but didn't find any SSIS package there. Could you please give some more suggestions? Thanks a lot.

regards,

Oliver

Answer 3

Hi Jens,

Thanks for your reply. Actually 2 of the job parameters are as below:

Package Source: SQL Server

Server: DE-S-0157590\DE_S_0157590

I tried to connect to integration service "DE-S-0157590", and I expanded every folder in the object panel but didn't find any SSIS package there. Could you please give some more suggestions? Thanks a lot.

Hi Oliver_Wang,

According to your description, we need to verify if someone has delete the SSIS package which is used in your job, I recommend you run this job and check if the job can execute successfully. Or you can choose “Edit” in the job steps and check if the SSIS package exists in select an SSIS Package Box.

If the package indeed exist, we need to verify if you choose the right SQL Server instance. In addition, when you connect to integration service, we need to verify if your login account has permission to view the SSIS package and so on. There is an article about SQL Server Agent jobs for packages, you can review it.

http://msdn.microsoft.com/en-us/library/ms141701(v=sql.90).aspx

Regards,
Sofiya Li

---

Sofiya Li
TechNet Community Support

Answer 4

Do you've a version control system like TFS? If yes, you can also search for the package within it.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 5

Hi Sofiya,

Thanks for your reply. Actually I have the admin permission over the instance so whether I choose the correct SQL Server instance might be the culprit. But I can't think of where the mistake is because if I can use "DE-S-0157590\DE_S_0157590" to login the SSMS and have the permission to look around those jobs, I just don't understand someone can put a job in another server, for example "DE-S-0157591". that's why I was confused by this problem for a long time.

regards,

Oliver

Answer 6

Hi Visakh,

Thanks for your reply. Could you please provide more detail? for example, where I can find "TFS" or where I can download it. 

regards,

Oliver

Answer 7

Hi Sofiya,

Thanks for your reply. Actually I have the admin permission over the instance so whether I choose the correct SQL Server instance might be the culprit. But I can't think of where the mistake is because if I can use "DE-S-0157590\DE_S_0157590" to login the SSMS and have the permission to look around those jobs, I just don't understand someone can put a job in another server, for example "DE-S-0157591". that's why I was confused by this problem for a long time.

regards,

Oliver

It is not necessary that SSIS package should be in the same instance as where the job is. SSIS package can be stored in a different instance and you can still call it from a job created in your instance. In such case you would be able to make out where package is stored by opening up job step properties and looking into server or package path property depending on whether package is stored in SQLServer or file system

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 8

Hi Visakh,

Sorry for my late response because I was in a business trip. As I mentioned, the 2 parameters of the job are as below:

Package Source:

SQL Server Server: DE-S-0157590\DE_S_0157590

So to my understanding, the job was deployed in instance "DE-S-0157590\DE_S_0157590". But when I open integration service "DE-S-0157590" in SSMS, there is nothing. Could you please share more your ideas? Thanks.

regards,

Oliver

Answer 9

Since you are not able to find that the integration services(Make sure you look under all folders), as visakh said, it is possible that it might have been deleted.

Did you run the job ? Did you run the Job step where it calls the package. If it runs the job step successfully, package does exists and it is matter finding it. If it failed, whats the error message - anything like package not found - means, it does not exist on the server any more.  

Team Foundation Server (TFS) is like a source control where you can save your code/packages etc..It can store multiple versions of package. Does your company have a policy to checkin the packages/code to TFS or version control before deploying. you cannot do anything about it now, if you do not already have it in place.

---

Hope it Helps!!