SSIS 2012 - Conditional Header Location

Question

I need to create several thousand txt files using SSIS in an instance of SQL Server 2012.

The problem is that some of the txt files have checksums in the first two lines, and some others do not.

If the file has a checksum, its first line starts with "HEADER" - in this case I need to skip the first two lines and then read the column headings from line No. 3 and load the data starting from line # 4.

If the file does not have a checksum, column names will exist on line # 1, and the data starts on line # 2.

Now, my question is: what is the least invasive way to deal with such a scenario? I am lazy, so I am looking for minimal effort with maximum effects.

My ideas so far:

• Determine the existence of checksums using the C # scripting component and create two separate streams: one for each file type. Disadvantage: two almost identical streams (I'm not a big fan of redundancy)

• Use PowerShell first to get rid of the checksums from the files before starting the SSIS stream. Disadvantage: many files need to be rewritten (performance hit)

• Ask about StackOverflow. Downside: The StackOverflow community can be seen as ironic.

Any clues?

Answer 1

Here is the approach.

• Create 2 flat file connection managers and in development mode go to the sample file for each so you can customize the columns, etc.
• , , Header Rows to Skip 0, , , 2 , .
• DelayValidation true.
• Scripting To Detect, .
• Scripting Component , , , . : HasCheckSumRows
• Scripting .
• HasCheckSumRows , true, 2 , 1 , .
• 2 , 1 , , , .
• 2 , , , connectionstring .
• , Union All, . , (.. ), 1 .

, .

Answer 2

, , , . , 1 Checksum, , , , . Foreach . . # script . 2 1 , - . 4 (FileConnection, Dataflow, source, destination). ( ) , , ! () , . , 2000+ .. @Shiva , , .

, .. SSIS powershell script. # script, . , , , . # script, , . " " ssis, syste, , , /.

@xpil, 15 . , , 1 2 . # 2, SSIS script. system.file.io. script, , , dfts. script , SSIS, system.file.io , , SQLBulkCopy, , DFT, . , , DFT. SSIS script, , , , .

DFT.

 string contents = File.ReadAllText(_WFRFullPath);
    contents = Regex.Replace(contents, @"\r\n?|\n", crlf);
    File.WriteAllText(_SSISWorkspaceFullPath, contents);

regex .

script , , , . .

        SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
        sqlConnection.Open();

        SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection);
        bulkCopy.DestinationTableName = _stagingTableName;
        foreach (DataColumn col in _jobRecDT.Columns)
        {
            //System.Windows.Forms.MessageBox.Show(col.ColumnName);
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
        }


        bulkCopy.WriteToServer(_jobRecDT);

        sqlConnection.Close();

. , , .