2

I am very new to SSIS. I have inherited a bunch of databases and SSIS packages. I notice many jobs utilizing many different packages. When I look at an existing job and try to see where a package it is using is located I just see that it is in the "SSIS Packages" folder.

I know you can either deploy a package to the file system or as a database object(?). How can I tell which type of deployment a package has taken?

Also, in an existing job, an associated SSIS solution where multiple projects contain multiple packages utilized by this job, there is one package I needed to modify. It isn't currently scheduled as part of a step of the job. When I go to add the new step, I don't see as an option that I can choose this package. I assume it must not be deployed. So how do I deploy it and how do I make sure I deploy it the same way the other packages in this job were deployed? (Really - how do I not mess things up!?)

Improve this question
4
  • 1
    what version of sql server are you using ? Are you working with SSISDB (newer version of sql server ) ?
    – Kin Shah
    May 23, 2016 at 21:38
  • No I'm not in this instance. Old school 2008 r2 sp1 I believe from memory..
    – default_noob_network
    May 23, 2016 at 21:55
  • Why are you trying to add new step in a job if you want to modify a package?
    – karun_r
    May 24, 2016 at 0:13
  • I am needing to do both. There is a group of packages that are all scheduled in a job as separate steps. One package in particular was not scheduled and also is needing modified. After I modify I will need to schedule it as a new step in the job.
    – default_noob_network
    May 24, 2016 at 14:05

1 Answer 1

Reset to default
2

How can I tell which type of deployment a package has taken?

Look at the definition of your SQL Agent Job Step.

If it specifies /file then the package has been to deployed to the file system somewhere.

If it specifies /DTS then it's using a well known location on the SQL Server installation path (precise location eludes my memory as of this posting but \Program Files\Microsoft SQL Server\XXX\DTS\Packages and/or C:\Program Files (x86)\Microsoft SQL Server\XXX\DTS\Packages are good guesses)

If it specifies /sql then it is deployed to the specified SQL Server in msdb.dbo.sysdtspacakges90 (SQL Server 2005) or msdb.dbo.sysssispackages (SQL Server 2008/2008R2+)

As to scheduling a package that you can't see, this sounds suspiciously like you're not on 2008 R2 as you think but much more like 2012+ as this is the SSISDB with the folder/project/package structure. If this is the case, packages can be specified as being Entry Point Packages or not. It's just a UI convention, there's nothing that prevents them from running. There's a selector that specifies whether you want Entry Point Packages or All. Switch to All packages

Improve this answer
1
  • The version of SQL server I see is 10.0.5500. And if it matters, I use Visual Studio 2008 BIDS to mess with the packages. Also, on the definition - where is this found? I see the Command Line tab under properties for the job says /SQL then gives a non helpful path. The same path I see on the Package tab.
    – default_noob_network
    May 24, 2016 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.