How to list all SSIS packages on the Sql Server 2008 using T-SQL -


i have no access connect ssis subsystem via sql management studio, looking way list ssis packages via t-sql. found the following query sql server 2005, not working 2008:

-- list ssis packages stored in msdb database.  select pck.name packagename        ,pck.[description] [description]        ,fld.foldername foldername        ,case pck.packagetype              when 0 'default client'              when 1 'i/o wizard'              when 2 'dts designer'              when 3 'replication'              when 5 'ssis designer'              when 6 'maintenance plan'              else 'unknown' end packagetye        ,lg.name ownername        ,pck.isencrypted isencrypted        ,pck.createdate createdate        ,convert(varchar(10), vermajor)         + '.' + convert(varchar(10), verminor)         + '.' + convert(varchar(10), verbuild) version        ,pck.vercomments versioncomment        ,datalength(pck.packagedata) packagesize  msdb.dbo.sysdtspackages90 pck       inner join msdb.dbo.sysdtspackagefolders90 fld           on pck.folderid = fld.folderid       inner join sys.syslogins lg           on pck.ownersid = lg.sid  order pck.name;

after little bit of research figured out query 2008, i'd share. see answer below.

this query works sql server 2008. main difference use msdb.dbo.sysssispackages , use left joins since packages may have no owner or associated folder.

select pck.name packagename        ,pck.[description] [description]        ,fld.foldername foldername        ,case pck.packagetype              when 0 'default client'              when 1 'i/o wizard'              when 2 'dts designer'              when 3 'replication'              when 5 'ssis designer'              when 6 'maintenance plan'              else 'unknown' end packagetye        ,lg.name ownername        ,pck.isencrypted isencrypted        ,pck.createdate createdate        ,convert(varchar(10), vermajor)         + '.' + convert(varchar(10), verminor)         + '.' + convert(varchar(10), verbuild) version        ,pck.vercomments versioncomment        ,datalength(pck.packagedata) packagesize  msdb.dbo.sysssispackages pck       left join msdb.dbo.sysssispackagefolders fld           on pck.folderid = fld.folderid       left join sys.syslogins lg           on pck.ownersid = lg.sid  order pck.name;

Comments

Post a Comment

Popular posts from this blog

php - Submit Form Data without Reloading page -

i know question has been asked , answered bunch of times life of me can not solution work on code. believe if redo code, work properly, , in retrospect time i've spent on issue should have redone code...but beyond deadline , have working prototype up... can advise how update multiple forms on single page without reloading page? horrible @ php , complete noob js/jquery/ajax @ best. html/php in single file , believe causing me problems/is largest obstacle @ getting ajax form processing work. basically, want users to: select teams multiple dropdowns in form group 1 (league champs) , on update update db newly selected team without reloadding page. edit team scores per match , on score submit/update, update db new scores. for each requirement there multiple forms (1 each leagues, 1 each per match) have set class on each form id. everything works via php, form submission reloads page each time submitted , not ideal. i have tried ajax code here http://scotch.io/tutoria...
Read more

linux - Rails running on virtual machine in Windows -

the reason why want switch ubuntu people said gems works better on linux on windows. i don't want macbook pro rails development since love windows , cannot stand lack of apps mac. moreover, people saying ubuntu "better" mac: http://www.reddit.com/r/ubuntu/comments/1bgj1s/are_there_advantages_of_ubuntu_over_os_x_for/ does virtual box work enough doesn't have lag @ all? using i7-3517u cpu 8gb memory. need program in environment lag-free , not sluggish. or there alternatives recommend? i unsure permission settings or complicated stuffs regarding terminal. i'll using terminal develop. i'm still quite new here development in linux. please kind me >< thanks. @holyxiaoxin pretty sure should asking on superuser instead. nevertheless, suggest install ubuntu in partition instead of running virtual machine. here's guide https://help.ubuntu.com/community/windowsdualboot also, yes. developing in virtual machine accurate, @ end experiment w...
Read more