locked
Pick out the SSIS package from a job RRS feed

  • 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

    Monday, July 7, 2014 5:36 AM

All replies

  • 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

    Monday, July 7, 2014 5:44 AM
  • 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

    Monday, July 7, 2014 8:42 AM
  • 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

    Tuesday, July 8, 2014 8:24 AM
  • 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

    Tuesday, July 8, 2014 8:36 AM
  • 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


    Monday, July 14, 2014 1:01 AM
  • 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

    Monday, July 14, 2014 1:03 AM
  • 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

    • Proposed as answer by Sofiya Li Wednesday, July 16, 2014 8:27 AM
    Monday, July 14, 2014 1:54 AM
  • 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

    Sunday, July 20, 2014 1:54 PM
  • 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!!

    • Edited by Stan210 Sunday, July 20, 2014 3:02 PM
    Sunday, July 20, 2014 3:00 PM