Can we have a folder as input path for SSIS package, and process all files within it?

Can we have a folder as input path for SSIS package, and process all files within it?

My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?

Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1

Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.

Regards,
MS Guy

MS Guy wrote:

Can we have a folder as input path for SSIS package, and process all files within it?

My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?

Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1

Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.

Regards,
MS Guy

There's a few things that make the answer "It depends" but there's a few things you should look at:
1) MULTIFLATFILE connection manager. This connection manager points to a colelction of files (usually all files in a folder) and you can then apply a single operation (such as loading the contents of all those files into a table) in one step. Usually the metadata of the files needs to be the same - it depends on what operation you're applying.

2) Use a Foreach loop container to loop over the files and process them individually. There's a bit more guidance on this here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx plus Darren and Allan have some good stuff on this at www.sqlis.com.

-Jamie

Can We assign DataSet object to Object Type

Hi Friends,

I am having a Package scope variable DS of type system.object .In the Script Component which accept this variable as ReadWrite type I am Making the following assignment.

Public Overrides Sub PostExecute()

Variables.DsReport = DsReport // DsReport is a Data Set Object Created and Populated inside the script Component

End Sub

I am getting error while doing this sort of assignment.

Please help me to solve this problem

Regards,

Mahe

Which error do you get?

-Tom

Can Triggers inititate an SSIS package run?

All:

I have a need to initiate an SSIS package based upon additions/changes that would be made to a db table. Can this be done and if so how?

Thanks!

Sure. Use xp_cmdshell to call dtexec.exe. Or, set up a SQLAgent job and call it using sp_start_job.

-Jamie

|||

Thanks Jamie! I have never worked with Triggers before but will give your solution a shot.

|||

Begin: (Stupid Question)

Where do I find sp_start_job

End;

|||Found it in msdb database. Thanks!

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!

Can SQL Server DTS Package be access thru Delphi?

I have defined a Data Transformation Services (DTS) package in MS SQL Server. In VB I know that the DTS execution can be done thru VB codings. But what about Delphi?
Can anybody suggest any Delphi components or codings for this purpose.
Thannks in advance.Don't know about Delphi, but if you know how to call SQL objects then follow the same method to call DTS packages also.

See whether http://www.sqldts.com is any help to you for information in this regard.|||i don't know Delphi
but if Delphi supports COM there is no problem...|||Using COM you can call DTS package or use activex to call it.