How to Get folder name and file name error in SSIS when using foreach loop

Question

I use SSIS to insert data to database. I use foreach loop to loop folder and files in that folder. I want to get folder name and file name that have the error in process. And i will log them in txt file. Everyone help me, Thank you !

Answer 1

An overview of this below. Some things may vary depending on your environment and how you want to log the names of the folders and files that had errors, but this should get you started in the right direction.

• Create an int variable, you can call this "ErrorCount" and make sure its value is set to 0. This will be used later in the error logging.

• Create an Execute SQL Task that will create a table which will be used to holder the folder and file name. "Filename" is a keyword in T-SQL so identifiers (square brackets) are used. These aren't essential, but make it easier to read.

• Since you're already using a Foreach Loop to load the files, I'm assuming a variable has already been mapped to hold the file name for each iteration. If you haven't done this, add a string variable at index 0 on the Variable Mappings pane of the Foreach loop.

• On the Data Flow Task that's loading the files add an OnError Event Handler with a C# Script Task. On the Script Task editor, add the string variable holding the file name (index 0 in the Foreach Loop) in the ReadOnlyVariables field. In the ReadWriteVariables field add the "ErrorCount" variable you created earlier and the System::Propagate system variable. This will be used later to allow subsequent files to be processed after an error.

• The code for this Script Task is later in this post. You'll need to add references to the System.IO and System.Data.SqlClient namespaces. An OnError event fires once for each error, not just when there was a file with an error. In this example, the file name is only logged if the "ErrorCount" user variable has a value of 0. Once the file and folder are logged this variable is set to 1 to avoid having the same file logged multiple times. The Path.GetDirectoryName and Path.GetFileName methods are used to get the folder and file names, respectively. While all the files you're loading are very likely to have the same extension, Path.GetFileName is still used to return the file extension with the name of the file. If you don't want to see the file extension, use the Path.GetFileNameWithoutExtension method instead.

• SSPI is set for Integrated Security to indicate that Windows Authentication will be used. To use SQL Server Authentication, set this to false and add the appropriate values for the User ID and Password properties. I'd recommend using parameters, i.e. the SqlParameter objects below, instead of constructing the SQL command as a concatenated string. Input is the default ParameterDirection but these is still defined for clarity. This script is taking the folder and file names that were parsed and inserting them into a table, in this example named ErrorFiles, that will be used to hold files that had errors during package execution before these are written to a file. While doing an insert with an Execute SQL Task would be simpler, the methods in the System.IO namespace allow for the folder and file name to be obtained correctly if the file path changes. If the folder you use never changes, an Execute SQL Task could be used with the T-SQL SUBSTRING function in the insert to parse the folder and file name from the variable that holds these.

• After the Data Flow Task, add another Script Task in the Foreach Loop. Connect this to the Data Flow Task and change the evaluation operation on the Precedence Constraint to Expression and add the expression below. This will ensure that the following Script Task will only run if there was an error during that iteration of the Foreach Loop by checking the value of the "ErrorCount" variable.

• In this Script Task, add the "ErrorCount" in the ReadWriteVariables field and similar to setting this to 1 earlier, now set it back to 0 (Dts.Variables["User::ErrorCount"].Value = 0). This is done to allow files that result in error in later iterations of the Foreach Loop to be logged as well.

• After the Foreach Loop, add a Data Flow Task with an OLE DB Source. Set the data access mode to SQL Command and select the FolderName and FileName columns from the table used to store the files that had errors. If you didn't created this table separately, you can set ValidateExternalMetadata to false and add the output columns in the External and Output Columns folder from the Input and Output Properties tab of the Advanced Editor. However if you're not familiar with this it may be easier to just run the CREATE TABLE DDL and define the metadata this way. In this Data Flow Task add a Flat File Destination that's connected to the OLE DB Source. Create a new Flat File Manager with name of the file that you want to use to hold the names of files that contained errors. You may want to use an expression in the file name to make it unique for each time the package is executed. An example expression is later in this post. This just uses the current date. If you plan to run this multiple times in a single day you might want to add a timestamp or otherwise to make each file name unique. This can be set as the name of the output file by adding this expression in a variable and using this as the ConnectionString property of the Flat File Manager.

• Next add an Execute SQL Task following this Data flow Task. For the SQLStatement add SQL to drop the table used to hold the names of files that contained errors if it exists. While this isn't necessary since the initial task that creates this table drops it, this avoids having an extra table in your database.

Precedence Constraint Expression:

@[User::ErrorCount] > 0

Flat File Connection Manager Connection String Expression:

"C:\\Your Folder\\" + (DT_STR, 4, 1252)DATEPART("Year", GETDATE()) + "_"
 + (DT_STR, 2, 1252)DATEPART("Month", GETDATE()) + "_" 
 + (DT_STR, 2, 1252)DATEPART("Day", GETDATE()) +"_"
+ "ErrorFiles.txt"

Error File Table DDL:

IF(OBJECT_ID(N'YourDatabase.DBO.ERRORFILES') IS NOT NULL)
BEGIN
DROP TABLE YourDatabase.DBO.ERRORFILES
END

CREATE TABLE YourDatabase.DBO.ERRORFILES 
(
FOLDERNAME VARCHAR(100),
[FILENAME] VARCHAR(100)
)

OnError Event Handler Script Task Code:

 if (Convert.ToInt32(Dts.Variables["User::ErrorCount"].Value.ToString()) == 0)
{
    string connString = @"Data Source=YourSQLServerInstance;Initial Catalog=YourDatabase;Integrated Security=SSPI";

    string cmd = @"Insert into dbo.ErrorFiles (FolderName,[FileName]) values (@folderName, @fileName)";

    //parse variable with file that caused error
    string errorFileFullName = Dts.Variables["User::NameForTable"].Value.ToString();

    //get folder
    string errorFolderName = Path.GetDirectoryName(errorFileFullName);
    //get only file name
    string errorFileName = Path.GetFileName(errorFileFullName);

    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand sql = new SqlCommand(cmd, conn);

        SqlParameter pFolderName = new SqlParameter("@folderName", SqlDbType.VarChar);
        pFolderName.Direction = ParameterDirection.Input;
        pFolderName.Value = errorFolderName;
        pFolderName.Size = 100;

        SqlParameter pFileName = new SqlParameter("@fileName", SqlDbType.VarChar);
        pFileName.Direction = ParameterDirection.Input;
        pFileName.Value = errorFileName;
        pFileName.Size = 100;

        sql.Parameters.Add(pFolderName);
        sql.Parameters.Add(pFileName);

        conn.Open();

        sql.ExecuteNonQuery();

        //avoid failing Foreach Loop so other files are processed
        Dts.Variables["System::Propagate"].Value = false;

        //prevent event handler from firing multiple times. 
        Dts.Variables["User::ErrorCount"].Value = 1;

    }
}