Can SSIS package be run from WebService?

Dear,

Can SSIS package be run from WebService?

P.S. SSIS package and Webservice are located in the same computer.

Thanks much!

It is solved.

And 1 more quest. How can I check the saved SSIS package in Microsoft SQL Server Management Studio?

Thanks much!

|||

Yes. When you connect to a server from SQL Server Management studio - change the Server type from 'Database Engine' to 'Integration Services'

Hope that helps.

Thanks,
Loonysan

|||

Thanks for your reply.

Once I click the MSDB folder, the error message, Login timeout expired is shown.

Do you have any idea about it?

Thanks

|||

Hi Michael,

The follwoing URL - http://emea.windowsitpro.com/SQLServer/Article/ArticleID/46723/46723.html
(Look at Roles section) talks about the various security permissions required to access/execute SSIS packages.

If this doesn't help - Please post this question in a new thread.

Thanks,
Loonysan

|||

Sorry I don't find "Roles Section" mentioned in the URL u post to me.

Could u mind explanning to me or give me another URL for me to reference?

Thanks much!

|||

Search for "Roles" in that URL - the second found result is the start of Roles section.

Hope that helps.

Thanks,
Loonysan

|||

Sorry.. I just found 1 "Roles" in the URL, http://emea.windowsitpro.com/Articles/Index.cfm?ArticleID=46723&DisplayTab=Article u given.

The content is as the below

Security in SSIS

Like every other feature in SQL Server 2005 Integration Services (SSIS), the product's new security features are noticeably different from their DTS counterparts. SSIS still uses passwords and encrypts sensitive data, but the approach has changed substantially, simplifying automated package execution, protection, scheduling, and editing. SSIS security features fall into five functional categories: encryption, for securing packages or parts of packages; sensitive-data protection, for identifying and protecting passwords and other sensitive data; SQL Server roles, for controlling access to packages stored in SQL Server; digital code signing, for ensuring that a package hasn't changed; and integration of SQL Server Agent subsystems, for securely storing and executing packages. Let's take a closer look at these new security features and get some guidelines for how and when to use them. I'd like to extend special thanks to Sergei Ivanov, the Integration Services team developer who wrote these features, for answering all my questions and making sure I got the details right. . . .

Thanks for your help!

|||

Not sure why you aren;t able to see the following text in that URL

Roles

SSIS adds three new roles that affect the way you run packages in SQL Server Agent and access packages stored in SQL Server. The db_dtsoperator role is the most limited new role. Users in this role can only enumerate (i.e., determine which packages are available) and view existing packages; operators can't create or modify SSIS packages. The db_dtsltduser role lets users create and modify their own packages and enumerate existing packages. And the db_dtsadmin role lets users create, modify, enumerate, and view all packages. Systems administrators are automatically in the db_dtsadmin role.

You must be in one of these three roles to access SSIS packages. If you aren't, and you attempt to enumerate a package by opening the Packages node for the SSIS Server object explorer in SQL Server Management Studio, you'll get the error message that Figure 2 shows.

You can find the new SSIS roles in the Management Studio MSDB database node under the Security, Roles, Database roles node. These roles apply only to MSDB and control access to the sysdtspackages90 table through the stored procedures that Figure 3 shows. You can find sysdtspackages90 and the stored procedures for accessing it in MSDB.

If you look at the sysdtspackages90 table in MSDB, you'll see two columns of particular interest to this discussion. The readrole column specifies the logins or roles that have read access to the package. Readers can enumerate, execute, and export a package from SQL Server. The writerole column specifies the logins or roles that have write access to the package. Writers can delete, rename, edit, and save a package to SQL Server. By default, these two columns are null.

To open the Package Roles dialog box, right-click a package in the Packages node and select Package Roles, as Figure 4 shows. Figure 5 shows the Package Roles dialog box default settings. When the readrole column is null, all users in any SSIS package role can enumerate and read that package. When the writerole field is null, only users in the db_dtsadmin role and the package's creator can modify or delete the package. To prevent a role from viewing and executing a given package, you must remove the role from the Reader Role comma-separated list. You can also add other roles to the reader and writer role lists. For example, I can add the Agent User, Limited User, and Security Administrator roles to the package reader role by entering them in a comma-delimited list as SQLAgentUserRole, db_dtsltduser, and db_securityadmin. I can also assign users and roles to a package's writer role to let them perform write operations such as DELETE and MODIFY to that package only.

To better understand how these roles interact and see which role has what rights, take a look at Table 1, which shows the rights assigned to each role and the activities that a user in each role can do by default. The table shows that the db_dtsoperator role can't work with its own packages; this limitation exists because the db_dtsoperator role can create packages, so that role can't own any. The table also shows that the Writerole db_stsoperator can't import packages because you can't have a write role on a non-existent package. The Assigned User isn't really a role, but I included it because you can assign a SQL login or role to a package reader or writer role. For example, by default, the db_dtsoperator isn't assigned to the writer role and doesn't have write privileges for packages. However, operators you explicitly place in the writer role will have all write-oriented privileges for that package.

By now your head is probably swimming in roles, but you need to know about one more new role in SQL Server 2005: the SQLAgentUserRole. A user must be in this role to create SQL Server Agent jobs, and users in this role can manage only the jobs that they create. This role is important because it affects a common SSIS usage scenario. After they're built, tested, and deployed, many packages reside on a SQL Server box in a back room somewhere, and a SQL Server Agent job triggers them when they need to run. Because SQL Server Agent integration is important to the security of such packages, let's take a closer look at how it works.

|||

Thanks for ur help.

But I find that I don't have any db_dtsoperator role in the server roles in DB and I cannot select different authentication mode once I login into Integration Services.

Can you give me some ideas? Thanks much!