Como faço para criar e preencher uma tabela temporária de uma fonte de dados por meio de um DataTable C# sem nunca sair da tarefa de fluxo de dados do SSIS?

Depois de verificar isso por muitas horas, encontrei uma maneira de fazer isso. O principal a entender é que os nomes das colunas do Rowobjeto são alterados para que apenas as letras sejam mantidas, exemplo: my_column-1 btorna-se mycolumn1b. Se você mapear os nomes abreviados das colunas para encurtá-los também no outro lado, ou seja, os nomes das colunas originais da fonte de dados, isso funcionará.

Código

#region About me
/* We use a DataTable to store the data from the input rows.
 * In the PreExecute method, we dynamically create columns in the DataTable based on the input columns.
 * In the Input0_ProcessInputRow method, we add each input row to the DataTable.
 * In the PostExecute method, we use SqlBulkCopy to efficiently insert all rows from the DataTable into the temporary table.
 * Once that is done, we can add columns with `select` queries from the temporary table in mere SQL.
 * The last step is not shown here; it could be either in the PostExecute or in another Script Component.
*/
#endregion

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Diagnostics;
#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private SqlConnection conn = null;
    private static string _connectionString => @"myconnectionstring";
    private DataTable dt = new DataTable();
    //private SqlCommand command = null;

    public override void PreExecute()
    {
        base.PreExecute();
        conn = new SqlConnection() { ConnectionString = _connectionString };
        conn.Open();
        // create temp table, make sure it matches your input
        using (SqlCommand cmd = new SqlCommand(@"CREATE TABLE ##TempTable (
my_column1 varchar(50));", conn))
        {
            cmd.ExecuteNonQuery();
        }
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Create DataRow to store values
        DataRow dr = dt.NewRow();

        // Iterate over input columns to get original column names
        foreach (IDTSInputColumn100 inputColumn in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
        {
            // Get the original column name
            string originalColumnName = inputColumn.Name;

            // Add column to DataTable with the original column name
            dt.Columns.Add(originalColumnName, typeof(object));

            // Get modified column name for checking null values in Row object
            string modifiedColumnName = originalColumnName.Replace("_", "").Replace(" ", "").Replace("-", "");

            // Check if the column is null
            PropertyInfo isNullProperty = typeof(Input0Buffer).GetProperty(modifiedColumnName + "_IsNull");

            if (isNullProperty != null)
            {
                bool isNull = (bool)isNullProperty.GetValue(Row);

                if (isNull)
                {
                    // Handle null value as needed
                    // For example, you can set it to DBNull.Value
                    dr[originalColumnName] = DBNull.Value;
                    continue; // Skip to the next column
                }
            }

            // Get property with matching name from Row object
            PropertyInfo property = typeof(Input0Buffer).GetProperty(modifiedColumnName);

            // Check if the property is null
            object value = property.GetValue(Row);

            // Check if the value is null
            if (value == null)
            {
                // Handle null value as needed
                // For example, you can set it to DBNull.Value
                dr[originalColumnName] = DBNull.Value;
            }
            else
            {
                // Add value to DataRow
                dr[originalColumnName] = value;
            }
        }
        // Add populated DataRow to DataTable
        dt.Rows.Add(dr);
    }

    public override void PostExecute()
    {
        base.PostExecute();
        // here we would bulk copy data into the temp table
        // you may need to batch this operation or handle it differently based on your situation
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            // Now write that DataTable to the database 
            bulkCopy.DestinationTableName = "##tmpTable";
            bulkCopy.WriteToServer(dt);
        }
        this.conn.Close();
    }
}

Saída

A saída de uma tabela temporária é salva no banco de dados tempdb do seu servidor. Ele não é salvo no banco de dados em que você trabalha.

Consulte Como ver a tabela temporária criada por código no SQL Server? .

Primeiro, elogie você por abrir a pergunta, adicionando alguns detalhes e tentando uma resposta cuidadosa. Honestamente, sua resposta me fez pensar um pouco mais e poderia funcionar, desde que public class ScriptMainfosse criada uma vez no nível do DataSet, o que acredito ser provável, mas ainda não tentei.

No entanto, existem alguns pontos de design que você pode considerar em sua solução final.

• O SSIS foi projetado para passar tabelas da(s) origem(s) por meio de uma série de tarefas de transformação até o(s) destino(s). Portanto, existem componentes para consultar uma subconsulta (fonte original) e para adicionar colunas, calcular, transformar tudo sem script para a maioria dos casos de uso.
• Desempenho. Usar uma CTE/Subconsulta na definição de origem original pode ter um desempenho exponencialmente melhor, pois reduzirá o número de linhas/quantidade de dados que são realmente puxados para o servidor SSIS na memória/disco para serem processados ​​no restante das etapas.
• Input0_ProcessInputRow(Input0Buffer Row)foi projetado para ser uma transformação por linha e cuja saída pode ser consumida nas próximas etapas. Em outras palavras, ele foi projetado para adicionar as novas colunas ou transformar o conteúdo da coluna diretamente na linha original, o que então modifica o conjunto de dados tabular disponível para as próximas etapas do pacote.
• O uso do DataTable e da tabela temporária também pode levar a problemas de memória e desempenho em grandes conjuntos de dados.

Então, acho que, ao determinar o uso de tarefas de script por linha como esta, eu perguntaria

• Isso pode ser feito em um Derived Column Transformationou Lookup, Grouping, Conditional Splitetc
• Isso pode ser feito com fórmula SQL na consulta de origem original ou outra transformação
• C# me permitirá fazer algo que não posso fazer em SQL
• Existe uma ferramenta melhor para esta transformação específica

Definitivamente, já faz alguns anos que usei scripts, pois abandonei o SSIS em favor de outras ferramentas, dadas APIs modernas e arquiteturas de nuvem. No entanto, usar um script no SSIS era geralmente (não exclusivamente) para obter uma fonte de algum lugar que exigisse raspar um site, interagir com o Microsoft Exchange, fazer coisas que realmente não são ideais. Em processos SQL ETL e de arquivamento puro, o SSIS deve ser capaz de lidar com isso dentro de seus componentes nativos.