Thursday, September 16, 2010

Script Task to Read File

Problem: Reading a file using Script Task

Solution: I'll explain this by creating a new SSIS package, reading the contents of the file and store the results in a package variable using Script Task.
In this article, I have covered about how to Read File in Script using Visual Basic 2008 and Visual C#  2008. Below are the steps to achieve the goal:

STEP1: Create a new SSIS package (I have renamed it as ReadFile.dtsx) and add two package variables FilePath and FileContent as shown below:
FilePath is used to store the path of the file being read by script task. FileContent variable isa used to store the contents of the file.

STEP2: Drag & drop Script Task and double click to open Script Task Editor. Enter Script Task - Read File using C Sharp 2008 as Name in General tab. Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWriteVariables, and Microsoft Visual C# 2008 in ScriptLanguage in Script tab.

Note: Here I am using two script tasks to explain the solution in both Visual Basic and C# 2008.


STEP3: Click on Edit Script... button of Script Task Editor and paste following code:

using System.IO;
public void Main()
{
  String ErrInfo = "";
  String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
  try
  {
    Dts.Variables["User::FileContent"].Value = ReadFile(FilePath, ErrInfo);
    if (ErrInfo.Length > 0)
    {
      Dts.Log("Error while reading File " + FilePath, 0, null);
      Dts.Log(ErrInfo, 0, null);
      Dts.TaskResult = (int)ScriptResults.Failure;
    }
    else
    {
      MessageBox.Show(Dts.Variables["User::FileContent"].Value.ToString());
      //Here do whatever you want to do with file contents
      Dts.TaskResult = (int)ScriptResults.Success;
    }
  }
  catch (Exception e)
  {
    Dts.Log(e.Message, 0, null);
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}


public String ReadFile(String FilePath, String ErrInfo)
{
  String strContents;
  StreamReader sReader;
  try
  {
    sReader = File.OpenText(FilePath);
    strContents = sReader.ReadToEnd();
    sReader.Close();
    return strContents;
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
    return "";
  }
}

STEP4: Thats all. We are done with package development. The content of File will be stored in the package variable FileContent while executing the package. To test the output, execute this package, a message box will popup (I didn't comment the Messagebox in the code) as shown in below:

And here is the snapshot of actual file:



Below is the code for Visual Basic 2008.

Imports System.IO

Imports System.Text
 
Public Sub Main()
  Dim dataBytes() As Byte = Nothing
  Dim ErrInfo As String = "" 'Variable to store Error Information
  Try
    Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString
    Dts.Variables("User::FileContent").Value = ReadFile(FilePath, _
     ErrInfo).ToString()
    If ErrInfo.Length > 0 Then
      Dts.Log("Error while reading contents of file " & _
      FilePath, 0, dataBytes)
      Dts.Log(ErrInfo, 0, dataBytes)
      Dts.TaskResult = ScriptResults.Failure
      Exit Sub
    End If
    MessageBox.Show(Dts.Variables("User::FileContent").Value.ToString)
    Dts.TaskResult = ScriptResults.Success
  Catch Ex As Exception
    Dts.Log(Ex.Message, 0, dataBytes)
    Dts.TaskResult = ScriptResults.Failure
    Exit Sub
  End Try
End Sub


Public Function ReadFile(ByVal FilePath As String, _
Optional ByVal ErrorInfo As String = "") As StringBuilder
  Dim strContents As New StringBuilder
  Dim objReader As StreamReader
  Try
    objReader = New StreamReader(FilePath)
    strContents.Append(objReader.ReadToEnd())
    objReader.Close()
    Return strContents
  Catch Ex As Exception
    ErrorInfo = Ex.Message
    Return strContents
    Exit Function
  End Try
End Function

60 comments:

  1. using your VB example I get a ReadFile not declared error.

    ReplyDelete
  2. Hi Rick,
    before posting my articles, I developed and test. my package is working fine for both C# & Visual Basic.
    Also ReadFile is Function not a variable. Could you tell me exact error you got?

    ReplyDelete
  3. this is good
    but can u send me a easy method of writing script for message box

    ReplyDelete
  4. While writing an code for an msg box there is an red mark on the task that is show in an error as this task is compiled to pre defined script.can u expalin me ..

    ReplyDelete
  5. To display a message, use:
    MessageBox.Show("AnyString")

    ReplyDelete
  6. sorry sir, what i ment is that there is an import statement that i need it

    ReplyDelete
  7. Hi Hari Sir,

    This is good Example .But Its open only txt file rather then excel or word file.

    ReplyDelete
  8. Hi Hari,

    very nice post,could you please send me the package on naga272009@gmail.com

    Thanks,
    Nagababu

    ReplyDelete
  9. Very nice! Exactly what I needed to get around the 8000 character issue that SSIS has with some of it's tasks. And I only had to change the variable names and comment out the message box statements.

    Thank you very much,
    Kenney

    ReplyDelete
  10. excellent piece of information, I had come to know about your website,i have read some posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again.
    Using the Script Component in SSIS

    ReplyDelete
  11. A bewildering web journal I visit this blog, it's unfathomably heavenly. Oddly, in this present blog's substance made purpose of actuality and reasonable. The substance of data is informative
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    ReplyDelete
  12. QuickBooks Payroll Support Number
    has emerged one of the better accounting software that has had changed this is of payroll. Quickbooks Payroll Support contact number will be the team that provide you Quickbooks Payroll Support.

    ReplyDelete
  13. After following the above troubleshooting steps, you'll be able to fix printer problem in QuickBooks Customer Support Number. However, if you are facing any trouble or otherwise not in a position to perform the troubleshooting steps on your own own, avail our QuickBooks Support telephone number available twenty-four hours per day to solve any QuickBooks related issues instantly.

    ReplyDelete
  14. You might need advanced software. There must be a premier mix solution. Quickbooks Support Number often helps. Proper outsource is a must. You'll discover updates concerning the tax table. This saves huge cost. All experts usually takes place. A team operates 24/7. You get stress free. Traders become free. No one will blame you. The outsourced team will quickly realize all.

    ReplyDelete
  15. QuickBooks software package is developed this kind of a fashion that it will give you the most effective account management mention of this era. However, you can face the issue along with your QuickBooks software and begin trying to find the solution. You must not worries, if you're facing trouble utilizing your software you're going to be just a call QuickBooks Support Phone Number away to your solution.

    ReplyDelete
  16. The experts at our QuickBooks Enterprise Tech Support have the required experience and expertise to manage all issues pertaining to the functionality for the QuickBooks Enterprise.

    ReplyDelete
  17. Our support services are not restricted to your above list. A lot of individuals are successfully availing the outstanding attributes of our technical support desk for QuickBooks. With a huge client base, we now have achieved the 100% satisfaction rate from customers around the globe. You too can avail our support services on just a call. Don’t hesitate to call us at QuickBooks Tech Support Number in the event you encounter almost any technical bug in QuickBooks.

    ReplyDelete
  18. Our team at QuickBooks Pro Support contact number is ready beforehand to give you customer-friendly assistance in the event that you talk to a problem using QuickBook Customer Support Number. All of us is skilled, talented, knowledgeable and spontaneous. Without taking most of your time, our team gets you rid of all of the unavoidable errors with this software.

    ReplyDelete
  19. In spite of all of the well-known QuickBooks Premier features you might find difficulty at some steps. QuickBooks Support Phone Number is the foremost destination to call in the time scale of these crisis.

    ReplyDelete
  20. If you’re looking for small-business accounting solutions, the first thing experts and happy costumers will recommend you is QuickBooks by Intuit Inc. Intuit’s products for construction contractors include the Quickbooks Pro, Simple Start Plus Pack, Quickbooks Premier Contractor, and Quickbooks Enterprise Solutions: Quickbooks Support Phone Number.

    ReplyDelete

  21. While you are feeling that enough time is odd to call for help, just pick up your phone and dial us at QuickBooks Support Phone Number because we provide our support services 24*7.

    ReplyDelete
  22. Problems are inevitable and they also usually do not come with a bang. Our team at QuickBooks Help & Support is ready beforehand to provide you customer-friendly assistance in the event that you talk with a concern using QuickBooks Pro. Many of us is skilled, talented, knowledgeable and spontaneous. Without taking most of your time, our team gets you rid of most unavoidable errors for this software.

    ReplyDelete
  23. QuickBooks Payroll Support 2019 requires below described information to help you to customize desktop or online account for payment processing in quite a trusted manner. Over the following step, you'll find information through the prior service provider.

    ReplyDelete
  24. Well! If you’re not able to customize employee payroll in QuickBooks Payroll Support Phone Number which makes the list optimally, in QB and QB desktop, then read the description ahead. Here, you will get the determination of numerous kind of information what you’ve readily available for assisting the setup process with comfort.

    ReplyDelete
  25. We have been a QuickBooks Support Number provider that is pro-actively involved in offering seamless support services to users of QuickBooks Desktop Premier. We assist them to with all issues and errors encountered while running their form of QB Premier. We offer support services with the help of our in-house team of certified QuickBooks Support, delivering them for you with reduced delay to handle the explanation for the issue/error at all possible time.

    ReplyDelete
  26. QuickBooks Enterprise Support Phone Number We understand that your growing business needs your precious time which explains why we offer the most effective to the customers. Our technically skilled professionals are well regarded for smart technical assistance around the world.

    ReplyDelete
  27. QuickBooks Enterprise Support Phone Number is an accounting solution that is favorable for small to mid-sized businesses encapsulating all of the sections like construction, distribution, manufacturing, and retail.

    ReplyDelete
  28. Well! If you’re not in a position to customize employee payroll in QuickBooks Payroll Support Number while making the list optimally, in QB and QB desktop, then browse the description ahead. Here, you receive the determination of numerous kind of information what you’ve close by for assisting the setup process with comfort.

    ReplyDelete
  29. With automated features and tools comes with different issues and errors within the software. Trying to find a dependable QuickBooks Enterprise Support Phone number channel who are able to successfully deliver high quality tech support team services?

    ReplyDelete
  30. If you're in search of technical assistance in QuickBooks software then QuickBooks Tech Support Phone Number could be the wisest choice. Large numbers of population is utilizing our QuickBooks Desktop Support channel to seek expert help in QuickBooks across the globe. You are able to avail the unlimited benefits of our tech support team by giving us a call at our toll-free. If you are looking manual ways to resolve this issue, have the below recommended steps to resolve the problem in different scenario.

    ReplyDelete
  31. We suggest someone to join our services just giving ring at toll-free QuickBooks Enterprise Support Number make it possible for one to fix registration, installation, import expert and plenty of other related issues in to the enterprise version.

    ReplyDelete
  32. Quickbooks Support accords assistance to the QuickBooks users’ worldwide. The support team can be reached through various modes such as: phone support, email support, live chat, FAQ, QuickBooks community etc. Solving the Quickbooks related problems and issue Remotely .

    ReplyDelete
  33. QuickBooks, a software solution which is developed in such a manner that you can manage payroll, inventory, sales and every other need of a small business. Each QuickBooks software option would be developed relating to different industries and their demands to be able to seamlessly manage your entire business finance at any time and in one go. No need to worry if you should be stuck with QuickBooks issue in midnight as our technical specialists at QuickBooks Support is present twenty-four hours a day to serve you using the best optimal solution right away.

    ReplyDelete
  34. QuickBooks Enterprise provides end-to end business accounting experience. With feature packed tools and features, this application is effective at managing custom reporting, inventory, business reports etc. all at one place. Are QuickBooks Enterprise errors troubling you? Are you currently fed up with freezing of QuickBooks? If yes, you then have browsed off to the right place. QuickBooks Enterprise Support telephone number is successfully delivering the whole world class technical assistance for QuickBooks Enterprise at comfort of your house. We understand your growing business need and that's the key reason why we provide simply the best. We make sure to give worth of every penny by giving the customer friendly QuickBooks Enterprise Technical Support services that include-

    ReplyDelete
  35. QuickBooks technical help is present at our QuickBooks Support Phone Number dial this and gets your solution from our technical experts.

    ReplyDelete
  36. Why you ought to choose Quickbooks Support PhoneNumber The principal intent behind QuickBooks Support Phone Number is to provide the technical help 24*7 so as in order to avoid wasting your productivity hours. This is completely a toll-free QuickBooks client Service variety that you won’t pay any call charges.

    ReplyDelete
  37. The QuickBooks Payroll Support Phone Number team at site name is held accountable for removing the errors that pop up in this desirable software. We look after not letting any issue can be found in between your work and trouble you in undergoing your tasks.

    ReplyDelete
  38. you will get the determination of numerous type of information that which you’ve close at hand for assisting the setup process QuickBooks Payroll Support Number

    ReplyDelete
  39. For accessing this feature provided by them the customers have to contact their customer services of helpline page, where they can easily access this contractor edition. This QuickBooks Support USA contractor edition provides you many features that are why this edition is becoming much famous these days.

    ReplyDelete
  40. Every business wishes to get revenues all the time. But, not all of you will end up capable. Are you aware why? It is due to lack of support service. You'll be a new comer to the business enterprise and make lots of errors. You yourself don’t learn how much errors you will be making. When this occurs it is actually natural to possess a loss in operation. But, i am at your side. If you hire our service, you are receiving the best solution. We're going to assure you due to the error-free service. QuickBooks Help & Support is internationally recognized. You must come to used to comprehend this help.

    ReplyDelete
  41. Though these features be seemingly extremely useful as well as in fact these are typically so, yet there are lots of loopholes that will trigger a couple of errors. These errors may be resolvable at QuickBooks Toll Free Phone Number, by our supremely talented, dedicated and well-informed tech support team team.

    ReplyDelete
  42. QuickBooks Payroll Customer Service Number helps you to definitely resolve your entire technical and functional issues while looking after this well known extensive, premium and end-to-end business accounting and payroll management software. Our experts team at QuickBooks Payroll Support Phone Number is going to make you realize its advanced functions and assists anyone to lift up your business growth.

    ReplyDelete
  43. QuickBooks Enterprise Support contact number is assisted by an organization this is certainly totally dependable. It is a favorite QuickBooks Support has had about plenty of improvement in the area of accounting.

    ReplyDelete
  44. The printer produces printouts, which are either blank or not aligned. Hence, Yes, HP Printer Tech Support Team Number are durable and strong, but there are times when the user faces an HP printer, not printing problems.

    ReplyDelete
  45. Any QuickBooks user faces any kind of identified errors in their daily accounting routine; these errors can vary from one another to a big degree, so our dedicated QuickBooks Payroll Support Phone Number are very well loaded with their tools and expertise to provide most effective resolutions very quickly to our customers.

    ReplyDelete
  46. you’ll additionally visit QuickBooks Payroll Support Number web site to induce to understand additional concerning our code and its upgrades. you’ll scan in-depth articles concerning most of the errors and also how you can resolve them. Rectifying errors desires in-depth information regarding the device as well as its intricacies.

    ReplyDelete
  47. QuickBooks Payroll Customer Service Number helps one to resolve all your valuable technical and functional problems whilst looking after this well known extensive, premium and end-to-end business accounting and payroll management software. Our experts team at QuickBooks Payroll Support Phone Number USA is going to make you understand its advanced functions and assists someone to lift up your business growth.

    ReplyDelete
  48. Printer is growing high with its broad range of customers. The latest version of printer inventory measures are just amazing, and with different models, for you to deal with. For matching the various mindset of people, both wired, as well as wireless printers, are now available from retail outlets and online stores. It is always advisable to come in direct contact with the original manufacturing houses while buying a piece
    HP Printer Support Phone Number

    ReplyDelete
  49. Our clients come back to us several times. We keep every one of the data safe plus in secrecy. We're going to never share it along with other people. Thus, it is possible to rely on us in terms of just about any data. we now have a technique of deleting the power that you've put immediately from our storage. Thus, there's no risk of data getting violated. You should arrive at us with regards to a number of software issues. The satisfaction may be top class with us. It is simple to contact us in a QuickBooks Support Phone Number of ways. You're able to travel to our website today. It's time to have the best help.

    ReplyDelete
  50. The net is stuffed with faux numbers WHO decision themselves the QuickBooks Support Provider. you’ll value more highly to dial their variety however that might be terribly risky. you’ll lose your QuickBooks Company file or the code itself. dig recommends dialing solely the authentic QuickBooks Support Number.

    ReplyDelete
  51. You are able to dial the QuickBooks Customer Support Number to possess a spoken language utilizing the QuickBooks Specialists otherwise you can also talk to them by victimization the chat choice on our internet site.

    ReplyDelete
  52. Printer is growing high with its broad range of customers. The latest version of printer inventory measures are just amazing, and with different models, for you to deal with. For matching the various mindset of people, both wired, as well as wireless printers, are now available from retail outlets and online stores. It is always advisable to come in direct contact with the original manufacturing houses while buying a piece
    HP Printer Support Phone Number

    HP Printer Support Phone Number

    HP Printer Support Number

    HP Printer Support

    HP Envy Printer Support

    HP Printer Customer Support Number

    HP Printer Customer Support Phone Number

    HP Printer Helpline Number

    HP Printer Technical Support Number

    ReplyDelete
  53. Our direct toll-free number for QuickBooks Payroll Support Number is . Write to us how we makes it possible to create your business more productive. The QuickBooks solutions provide you with a selection to your office better with QuickBooks Payroll software.

    ReplyDelete
  54. QuickBooks software program is developed in such a manner that it will supply you with the best account management reference to this era. However, you could face the issue with your QuickBooks software and begin trying to find the clear answer. You should not worries, if you should be facing trouble using your software you'll be just a call away to your solution. Reach us at QuickBooks Support Phone Number at and experience our efficient tech support team of many your software related issues. If you're aa QuickBooks enterprise user, it is possible to reach us out immediately at our QuickBooks Tech Support Phone Number. QuickBooks technical help is present at our QuickBooks tech support number dial this and gets your solution from our technical experts.

    ReplyDelete

Here are few FREE resources you may find helpful.