Well, In that case the choice is entirely yours. You can write a code if you are more comfortable with it and logging can be taken care of by you as per your requirement. Some people would be inclined towards using SSIS for this too. Thats a personal choice.My Blog | Ask Me

Hi, In SSIS, we have out of box features available for file and FTP operations. Same operations can be achieved even by using VBScript. What is the advantage of using SSIS features over VBScripting. Developers who are professional in VBscripting and C# tend to do these operations using scripting. Is there any disadvantage if it is done with scripting? Please let me know. Regards,Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Let me start by saying tht all that can be done by SSIS can very much be done by Coding. Advantages of SSIS over coding: 1. Development time goes down. 2. Framework available for logging and event handlig(would be diffucult to handle all in a code) 3. Each component has been tested so we need to test the business transformation and not the components itself while testing. 4. Main difference Data Flow Task the core of ETL incorporates a lot of fetures for fast ETL like parallelism which would be very difficult to code for each requirement. You would have to delve in multi threading which not many are comfortable with. 5. SSIS is a move in forward direction so that we concentrate more on business requirement and less on development so why go back?? These are few of the things that come straight to my mind there will be a lot more points favouring SSIS over raw code. Yes at times there are a few scenarios where code will be much simpler like moving spefific files frm one foldeer to other. But this is not all thats done as a part of ETL. Think of all the complex scenarios that can come in ETL and thenthink of the amount of coding you would require to get that to work. Then how stable would be the cde? how much time would go towards testing the code......My Blog | Ask Me

I agree with Sudeep - and think that most people who comment in this forum would as well. However, this audience is probably biased towards that position. IMO, using the out-of-the-box tasks and components is much better than use of the Script objects, and should be preferred even if performance, time-to-code, or apparent complexity suffers somewhat. I say this for all the reasons Sudeep mentioned, but there are two in particular that stand out for me: Scripting is secondary to SSIS. This means a few things: Not every SSIS dev will know VB or C# code, so maintainability can become an issue. Script in SSIS is like C++ is to C#... "unmanaged". You can do wonderful things with it, but you can also easily shoot yourself in the foot without knowing it. The OOB objects prevent that 2005 had VB.Net (VSA), 2008 has VB.Net and C# (VSTA)... who knows what script support will be in the next version? There will (obviously) be one, and it will (obviously) be as good or better - I'm not FUDing here. I'm just saying it's possible that upgrades could be an issue. The second important part for me is plain-old maintainability: Doing the "same thing" in multiple packages with scripts results in the "same script" being copy and pasted everywhere. What happens when you want to change that script? You have to modify every package and modify every script. But you have to open and READ every script to see if it needs modification. Even though you'd probably have to open and alter the same packages if you used OOB objects, it's more likely that you'll be able to see and alter the package more easily. It's hard to maintain a script that you didn't write. It's much easier to maintain a visual pattern where you can see what's happening at design and runtime. When engine improvements come along, scripts get no benefits, but OOB objects will. And painlessly too. Don't get me wrong - I think we'd have killed MSFT a long time ago if they didn't have Script stuff in SSIS. It's pretty hard to write a "real" package without resorting to one. But it should be somewhat of a last resort, IMO. Talk to me now on

Thanks Sudeep for such a detailed information. Actually, the script component which I am referring to is peforming the file copy operation from one folder to another folder. As I understand, even this can be done with SSIS out of box features with file operations. Similarly there are certain FTP related operations that are being performed using scripting, which can well be handled with SSIS features. Your reply was exactly matching my thinking in terms of dev and maintenance efforts. However, in the end you mentioned the file operations as part of exception and that is what exactly is being done over here. So, my question remains the same as I have asked. Specific to file copy from one share folder to another is it preferred to use SSIS tasks (File System/FTP) or Scripting. Please suggest. Let me be more specific in what I am trying to ask. There are certain checks made as to whether the file is existing with the specific pattern, if so, then go ahead and copy it to another location i.e., destination and delete it from source. This can be very much achieved by the File system task. In such cases, I think usage of file system task is easier. Please advice. Also request you to throw somelight on the impact of using scripting/out of box SSIS features from performance perspective. Regards, Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Todd the point you mentioned about maintainability of the code is very relevant and as industry standard people tend to copy paste a code over and over again blindly. This will be a big issue if there is some change in the original code. My Blog | Ask Me