Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reading a file in SSIS

Posted on 2014-01-24
4
Medium Priority
?
386 Views
Last Modified: 2016-02-10
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
Comment
Question by:ADhal37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 2000 total points
ID: 39807037
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
 

Author Comment

by:ADhal37
ID: 39821429
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
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 2000 total points
ID: 39823657
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
 

Author Comment

by:ADhal37
ID: 39823736
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question