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

How to create Error Log inside the SSIS Package ??

I am working on SQL 2008 - SSIS Package. I am executing the package from C# Code. Its simple Flat File ( .csv) to SQL Table.

From the c# code, i get execution result as "SUCCESS". But inside the package Transactions have not taken, So values are not actually exported.

I need to debug it, by reading the log file.

How to create Error Log or Log File inside the Package. ??
0
chokka
Asked:
chokka
1 Solution
 
tigin44Commented:
if you are calling the ssis package from the command line you may use the -L option to get the log as explained in this link
 http://msdn.microsoft.com/en-us/library/ms162810.aspx

also this link will be very helpful for you if you want to log the ssis package activities within package..

http://msdn.microsoft.com/en-us/library/ms140246.aspx
0
 
SQLSergentMikeCommented:
If you want to enable logging on a package this covers how you can implement that in your packages with all the available options.

http://msdn.microsoft.com/en-us/library/ms138020.aspx

Otherwise, you can use a data viewer to see what values are being passed as a means to debug. I do this often and it is easy enough to implement. Just right click the transformations and select data viewer and click add. Your other option is to log the failed rows by taking the error path on the data flow (the red connector) and direct it to a file for output.
0
 
Alpesh PatelAssistant ConsultantCommented:
There are different events. Using those events you can Create a  Log.

There are events like Error, Warning, PreExecute ,Prost Execute is there. SAve the each row for step.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
chokkaAuthor Commented:
tigin44 : Thank you for comment. I mentioned on my Post clearly - I am calling SSIS Package from C# Source Code.

Below is the Source Code for invoking the SSIS Package !!!
DTSExecResult pkgResults = new DTSExecResult();
        try
        {
            string strSourceConn = @"C:\SourceFile\File1.csv";
            string pkgLocation = @"C:\SSIS\SSIS\SSIS\Package.dtsx";
           
            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app;

            app = new Microsoft.SqlServer.Dts.Runtime.Application();
            pkg = app.LoadPackage(pkgLocation, null);

            pkg.Variables["sSorceFileName"].Value = strSourceConn;
            
            pkgResults = pkg.Execute();
            Response.Write(pkg.ExecutionResult);
        }
        catch (Exception ex)
        {
            Response.Write(ex.InnerException.Message);
        }

Open in new window

0
 
chokkaAuthor Commented:
I kept a Script Task which is connected to DataFlow.

Inside the Script Task, I wrote

    MessageBox.Show(Dts.Variables.Contains("sDestConnStr").ToString());
            MessageBox.Show(Dts.Variables.Contains("sSorceFileName").ToString());
            MessageBox.Show(Dts.Variables.Contains("sTableName").ToString());

Message Box Pops up and shows - False

So, Values which i am passing from C# Source Code is not reaching the Data Flow Task
0
 
SQLSergentMikeCommented:
Can I ask one quick question? Is the variable in the package suppose to be "sSorceFileName"? or is it misspelled in both places?
0
 
chokkaAuthor Commented:
SQL Sergent Mike - It is supposed to be sSorceFileName

and we are following the same name every where !!!

Reason behind sSorceFileName was, one of our developer written the variable name every where ..! And i have to follow footsteps of my existing developer !!!
0
 
chokkaAuthor Commented:
Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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