• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Reading a file in SSIS

Hi,

We have an issue where we need to monitor a log file for an error code.  I thought there may be a way to do this in SSIS where it can read a line in a file?  I have seen an article about reading the SQL Error Log file but not specifically any file.

Hope you can point me in the right direction.

Many Thanks
Anita
0
ADhal37
Asked:
ADhal37
  • 2
  • 2
2 Solutions
 
Barry CunneyCommented:
Hi Anita,
In SSIS add a Script Task component.
The Script Task component facilitates writing VB.net or C# code

In the Script Task, for your situation, you could write C# code that uses the .Net StreamReader object to read the required file - Partial sample code below:

static void Main()
    {
	//
	// If you have an SSIS variable that has the file name, read in the value of this variable
	Logfile = Dts.Variables["LogFile"].Value


	string line;
	using (StreamReader reader = new StreamReader(LogFile))
	{
	    line = reader.ReadLine();
	}

Open in new window

0
 
ADhal37Author Commented:
Hi

Many Thanks for that.  I have written some code in VB that reads the file:

Public Sub Main()
            '
        ' Add your code here

        'initialize StreamReader class for text file'
        Dim streamReader As New IO.StreamReader("C:\reader.txt")
        ' Read the StreamReader To End and assign to local variable
        Dim StreamText As String = streamReader.ReadToEnd()
        ' assign SSIS variable with value of StreamText local variable.
        Me.Dts.Variables("vText").Value = StreamText
            '
            Dts.TaskResult = ScriptResults.Success
      End Sub

Could you please advise on how I can return an error or send an alert if there is a particular error code in the log file?
0
 
Barry CunneyCommented:
Hi Anita,
An option is to use the Dts.Events.FireError event which can log an error
Dts.Events.FireError(0, "Log File Error Check", "Error Code found in Log file", String.Empty, 0)


So your code could be something like the following

Public Sub Main()
            '
        
	Dim LogFile As String
	Dim ErrorCode As String
	
        LogFile = Me.Dts.Variables("LogFile").Value
	ErrorCode = Me.Dts.Variables("ErrorCode ").Value


        'initialize StreamReader class for text file'
        Dim streamReader As New IO.StreamReader(LogFile)
        
	' Read the StreamReader To End and assign to local variable
        Dim StreamText As String = streamReader.ReadToEnd()
        
	If StreamText.Contains(ErrorCode) Then
		
		Dts.Events.FireError(0, "Log File Error Check", "Error Code found in Log file", String.Empty, 0)
		
	End If
	
          
          Dts.TaskResult = ScriptResults.Success


End Sub

Open in new window



What exact type of alert is required in your case? Is an e-mail alert required?
0
 
ADhal37Author Commented:
That's great, I will give that a whirl.

Ideally, it could just log an error in the SQL error log and I could then build an alert to fire when it shows.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now