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 Visual Studio 2005 BI co-exist with Visual Studio 2003?

I ask because I'm still looking for the source of interface problems I have in using the SSIS designer.

Yes. I have the two side by side and develop projects of most types in both.

Not sure if there is an official guide to this but I have found in the past that it always pays to install the older software first, followed by the later version.

|||Thanks Darren. Just wanted to make sure I wasn't making a fundamental mistake.|||Can Visual Studio Orcas and Visual Studio 2005 coexist?sql

Can Visual Studio 2005 BI co-exist with Visual Studio 2003?

I ask because I'm still looking for the source of interface problems I have in using the SSIS designer.

Yes. I have the two side by side and develop projects of most types in both.

Not sure if there is an official guide to this but I have found in the past that it always pays to install the older software first, followed by the later version.

|||Thanks Darren. Just wanted to make sure I wasn't making a fundamental mistake.|||Can Visual Studio Orcas and Visual Studio 2005 coexist?

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 work with an XML web service? (Verisign Payflow Pro)

I'm trying to figure out a solution for posting financial transactions against our Payflow Pro (Verisign) payment gateway (web service) using SSIS. The process I have in my mind goes like this...

1.) Select the appropriate records from our financial system DB.

2.) Iterate through each record and post the pertinent values against the payment gateway web service.

3.) Create log files for successful and failed transactions.

The log files would then be manually imported into our financial system.

Thanks in advance.

There is no web service or XML data flow destination, unfortunately. You could write a custom destination component to call your web service.|||

jwelch wrote:

There is no web service or XML data flow destination, unfortunately. You could write a custom destination component to call your web service.

Not entirely true... You can stick a Web service task in a foreach loop that spins through the recordset. Then you merely map the appropriate variables to the Web service.

This is done on the control flow, though, so you'd need to perhaps stage your data first in the data flow and then select it with an Execute SQL task to load up your ADO recordset.|||My bad, I was focused on the data flow. Good approach.|||Great info! I'll look into that.

Can SSIS work in a Cluster

We have just installed a new SQL 2005 Clustered server and loaded integration services. However it appears that integrations services may not work properly in a clustered environment.

So can integration services be setup on a clustered SQL 2005 server?

Funny, someone asked the same question earlier today. Is it clustering day? :)

Go here anyway: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366352&SiteID=1

-Jamie

|||

How about if this is a multi instance cluster? In the config fil there is only room for 1 server/instance of SQL. <ServerName>K-CLUSTER2-SQL1</ServerName>. Can other instances use this SSIS service? Or is there additional configuration?

Thx

Can SSIS work in a Cluster

We have just installed a new SQL 2005 Clustered server and loaded integration services. However it appears that integrations services may not work properly in a clustered environment.

So can integration services be setup on a clustered SQL 2005 server?

Funny, someone asked the same question earlier today. Is it clustering day? :)

Go here anyway: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366352&SiteID=1

-Jamie

|||

How about if this is a multi instance cluster? In the config fil there is only room for 1 server/instance of SQL. <ServerName>K-CLUSTER2-SQL1</ServerName>. Can other instances use this SSIS service? Or is there additional configuration?

Thx

Can SSIS parse this text report without a lot of programming?

I've got some machines that output text files after each shot of parts. I'd like to take the data in those files and parse it and insert it into a SQL Server database for future massaging. The text files look like the example I've posted below. Can SSIS parse out the set points and actual values even though the file isn't CSV or tab delimited and the data is kind of 'strewn' all over the report? Each report does have the exact same format so the report format doesn't change from report to report, just the data. Thanks in advance.

Ernie

WP4.57 C Y C L E P R O T O C O L

Order data 18.05.06 11:27:57

Order number : 2006 Recipe no. : 15761

Machine number : 7 Recipe name : Stabilizer Bar Innsulator

Machine Operator: 1257 Art.descrip.: Stabilizer Bar Grommet

Shot Volume : 285.8

Part quantity : 100096 Type of mat.: M370-34

Shot quantity : 782 Batch number: 20124-125

-

Temperatures in ?C

Set Act Set Act

Fixed heat.platen right 182 182 Tempering screw 83 83

middle 180 180 Tempering inject.cylinder 85 85

left 182 182 Tempering circuit 3 90 91

Tempering circuit 4 0 39

Movab.heat.platen right 182 182 Tempering circuit 5 0 39

middle 180 180

left 182 182 Mould temperature 1 0 39

Mould temperature 2 0 39

Third heat.platen right 0 39 Mould temperature 3 0 39

middle 0 39 Mould temperature 4 0 39

left 0 39 Mould temperature 5 0 39

Mould heating circuit 6 0 39 Compound temp.after screw 104 104

Mould heating circuit 7 0 39 Compound temp.after nozzle 0 39

Mould heating circuit 8 0 39

Mould heating circuit 9 0 39

Mould heating circuit 10 0 39

Times in sec

Injection time 51.20 Transfer time 1 2.00

Internal mould press.time 0.00 Transfer time 2 2.00

Dwell pressure time 7.00 Transfer time 3 2.00

Controlled cure time 180.00 Transfer time 4 2.00

Calculated cure time 0.00 Transfer time 5 2.00

last cycle time 276

last opening time 25

Measure when injecting Measure at injection end

max. injection speed mm/s 11.9 Injection length mm 2.0

Injection energy kNm 247.2 Injection time sec 51.20

max. int.mould pres. bar 2 Hydraulic pressure bar 190

max. dwell pressure bar 192 Internal mould pressure bar 0

Pad mm 0.4

Stock Temperatures and Pressures During Metering

Stock Temperatures(C) Set Actual Metering Pressures(bar) Set Actual

Temperature 1st Step 105 106 Pressure 1st Step 135 131

Temperature 2nd Step 105 106 Pressure 2nd Step 135 129

Temperature 3rd Step 105 105 Pressure 3rd Step 135 122

Temperature 4th Step 105 106 Pressure 4th Step 135 135

Temperature 5th Step 105 109 Pressure 5th Step 135 137

Protocol Complete

Yes absolutely, SSIS can do this. Import it as a single, very wide, column and parse out the various sections in the pipeline. Given the complexity you're probably going to have to do this in an aysnchronous script component.

-Jamie

Can SSIS packages target SQL 2000 DBs?

It would seem like it could, but I don't see that mentioned explicitly
anywhere.
Beige Bond
http://www.aspnetworkshop.com/
Sure, no problem at all.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Beige Bond" <BeigeBond@.hotmail.com> wrote in message
news:elhiqt4PGHA.1728@.TK2MSFTNGP11.phx.gbl...
> It would seem like it could, but I don't see that mentioned explicitly
> anywhere.
> --
> Beige Bond
> http://www.aspnetworkshop.com/
>

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 SSIS do SFTP? and how?

I want to know whether SSIS can do SFTP. If yes, how does it work?

Thanks,

Kev

Yes

We are using Xceed FTP component in a Script Task (http://xceed.com/FTP_NET_Features.html)

Certain operations (zip, ftp, email) in SSIS are not built-in or doesn't suffice the requirements in which case you can extend the functionality using components, script tasks or custom-built tasks.|||

TPK1216 wrote:

I want to know whether SSIS can do SFTP. If yes, how does it work?

Thanks,

Kev

nSoftware have a suite of SSIS tasks, one of which is an SFTP task.

More here:

nSoftware releases IPWorks for SSIS
(http://blogs.conchango.com/jamiethomson/archive/2006/06/02/SSIS_3A00_-nSoftware-releases-IPWorks-for-SSIS.aspx)

-Jamie

|||We have recently released SFTP SSIS task for secure communications. It has user interface and functionality very similar to the standard FTP task. You may check it here: http://www.cozyroc.com/products.html

Regards,
Ivan

Can SSIS do SFTP? and how?

I want to know whether SSIS can do SFTP. If yes, how does it work?

Thanks,

Kev

Yes

We are using Xceed FTP component in a Script Task (http://xceed.com/FTP_NET_Features.html)

Certain operations (zip, ftp, email) in SSIS are not built-in or doesn't suffice the requirements in which case you can extend the functionality using components, script tasks or custom-built tasks.|||

TPK1216 wrote:

I want to know whether SSIS can do SFTP. If yes, how does it work?

Thanks,

Kev

nSoftware have a suite of SSIS tasks, one of which is an SFTP task.

More here:

nSoftware releases IPWorks for SSIS
(http://blogs.conchango.com/jamiethomson/archive/2006/06/02/SSIS_3A00_-nSoftware-releases-IPWorks-for-SSIS.aspx)

-Jamie

|||We have recently released SFTP SSIS task for secure communications. It has user interface and functionality very similar to the standard FTP task. You may check it here: http://www.cozyroc.com/products.html

Regards,
Ivan

Can one SSIS packet1 run SSIS packet2?

Hi,

If I have 2 SSIS packets, can one start the other?

Thank you.

You can use the Execute Package task in the control flow to execute another SSIS package.|||

You can definitly use an Execute Package Task to execute the package you need... Also you may want to read something about package configurations... so that you can learn how to get parent variables passed to the child one... and so on

Hope u are on ur way there!

Regards