Как получить имя папки и ошибку имени файла в SSIS при использовании цикла foreach

Question

Я использую SSIS для вставки данных в базу данных. Я использую цикл foreach для зацикливания папки и файлов в этой папке. Я хочу получить имя папки и имя файла с ошибкой в ​​процессе. И я буду регистрировать их в текстовом файле. Все, помогите мне, спасибо!

Answer 1

Обзор этого ниже. Некоторые вещи могут различаться в зависимости от вашей среды и того, как вы хотите регистрировать имена папок и файлов, в которых были ошибки, но это должно помочь вам начать работу в правильном направлении.

• Создайте переменную int, вы можете вызвать это значение "ErrorCount" и убедиться, что его значение установлено равным 0. Это будет использовано позже в журнале ошибок.

• Создайте задачу "Выполнение SQL", которая создаст таблицу, которая будет использоваться для хранения папки и имени файла. "Имя файла" - это ключевое слово в T-SQL, поэтому используются идентификаторы (квадратные скобки). Они не обязательны, но облегчают чтение.

• Поскольку вы уже используете цикл Foreach для загрузки файлов, я предполагаю, что переменная уже сопоставлена ​​для хранения имени файла для каждой итерации. Если вы этого не сделали, добавьте строковую переменную с индексом 0 на панели "Сопоставление переменных" цикла Foreach.

• В задаче потока данных, которая загружает файлы, добавьте OnError Обработчик событий с задачей C# Script. В редакторе задач сценария добавьте строковую переменную, содержащую имя файла (индекс 0 в цикле Foreach) в ReadOnlyVariables поле. в ReadWriteVariables поле добавьте переменную "ErrorCount", которую вы создали ранее, и System::Propagate системная переменная. Это будет использовано позже, чтобы разрешить обработку последующих файлов после ошибки.

• Код для этой задачи скрипта позже в этом посте. Вам нужно будет добавить ссылки на System.IO а также System.Data.SqlClient Пространства имен. Событие OnError запускается один раз для каждой ошибки, а не только тогда, когда был файл с ошибкой. В этом примере имя файла регистрируется только в том случае, если пользовательская переменная "ErrorCount" имеет значение 0. После того, как файл и папка зарегистрированы, эта переменная устанавливается в 1, чтобы избежать одновременной регистрации одного и того же файла несколько раз. Path.GetDirectoryName а также Path.GetFileName методы используются для получения имен папок и файлов соответственно. Хотя все загружаемые вами файлы, скорее всего, имеют одинаковое расширение, Path.GetFileName все еще используется, чтобы возвратить расширение файла с именем файла. Если вы не хотите видеть расширение файла, используйте Path.GetFileNameWithoutExtension метод вместо.

• SSPI установлен для встроенной безопасности, чтобы указать, что будет использоваться проверка подлинности Windows. Чтобы использовать проверку подлинности SQL Server, установите для этого параметра значение false и добавьте соответствующие значения для User ID а также Password свойства. Я бы рекомендовал использовать параметры, то есть SqlParameter объекты ниже, вместо того, чтобы создавать команду SQL в виде объединенной строки. Ввод по умолчанию ParameterDirection но они все еще определены для ясности. Этот скрипт берет проанализированные имена папок и файлов и вставляет их в таблицу, в этом примере с именем ErrorFiles, который будет использоваться для хранения файлов, которые имели ошибки во время выполнения пакета, прежде чем они будут записаны в файл. Хотя вставка с помощью задачи "Выполнение SQL" будет проще, методы в System.IO Пространство имен позволяет правильно получить имя папки и файла при изменении пути к файлу. Если папка, которую вы используете, никогда не изменяется, можно выполнить задачу "Выполнение SQL" с T-SQL. SUBSTRING Функция во вставке для анализа папки и имени файла из переменной, которая содержит их.

• После задачи потока данных добавьте еще одну задачу сценария в цикл по каждому элементу. Подключите это к Задаче потока данных и измените операцию оценки в Ограничении приоритета на Выражение и добавьте выражение ниже. Это обеспечит выполнение следующей задачи сценария только в том случае, если во время этой итерации цикла по каждому элементу возникла ошибка, проверив значение переменной ErrorCount.

• В этой задаче сценария добавьте "ErrorCount" в ReadWriteVariables поле и аналогично установке этого в 1 ранее, теперь установите его обратно в 0 (Dts.Variables["User::ErrorCount"].Value = 0). Это сделано для того, чтобы файлы, которые приводят к ошибке на последующих итерациях цикла Foreach, также регистрировались.

• После цикла по каждому элементу добавьте задачу потока данных с источником OLE DB. Установите для режима доступа к данным команду SQL и выберите столбцы FolderName и FileName из таблицы, в которой хранятся файлы с ошибками. Если вы не создали эту таблицу отдельно, вы можете установить ValidateExternalMetadata в false и добавьте выходные столбцы в папку "Внешние и выходные столбцы" на вкладке "Свойства ввода и вывода" в расширенном редакторе. Однако, если вы не знакомы с этим, может быть проще просто запустить CREATE TABLE DDL и определите метаданные таким образом. В этой задаче потока данных добавьте пункт назначения плоского файла, который подключен к источнику OLE DB. Создайте новый диспетчер плоских файлов с именем файла, который вы хотите использовать для хранения имен файлов, содержащих ошибки. Вы можете захотеть использовать выражение в имени файла, чтобы сделать его уникальным при каждом выполнении пакета. Пример выражения приведен ниже в этом посте. Это просто использует текущую дату. Если вы планируете запускать это несколько раз в течение одного дня, вы можете добавить временную метку или иным образом сделать каждое имя файла уникальным. Это может быть установлено как имя выходного файла, добавив это выражение в переменную и используя его как ConnectionString свойство диспетчера плоских файлов.

• Затем добавьте задачу "Выполнение SQL", следуя этой задаче потока данных. Для SQLStatement добавьте SQL, чтобы удалить таблицу, в которой хранятся имена файлов с ошибками, если таковые существуют. Хотя в этом нет необходимости, поскольку первоначальная задача, которая создает эту таблицу, удаляет ее, это позволяет избежать наличия дополнительной таблицы в вашей базе данных.

Выражение ограничения предшествования:

@[User::ErrorCount] > 0

Строка выражения диспетчера соединений с плоскими файлами:

"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"

Таблица файлов ошибок 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:

 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;

    }
}