none
SSIS Programmaing - How to map flatfile source columns to SQL Server destination?

    Question

  • Hi,

     

    I am new to SSIS programming and trying to export data from a flatfile source to SQL server destination table dynamically. I need to get the table schema info (column length, data type etc.) from SQL server table and then map the source columns from flatfile to destination table columns.

     

    I am referring to one of the programming samples from Microsoft and another excellent article by Moim Hossain. Can someone help me understand how to map the Source columns to destination table columns depending on table schema? Please help.

     

    Thanks


    Friday, December 07, 2007 3:20 PM

Answers

  • Try to use SMO against your table, with SMO you can obtain all metadata which you need from the database.

     

    Microsoft.SqlServer.Management.Smo

    Microsoft.SqlServer.ConnectionInfo

     

    and few others (Enum, Types, ...) namespaces|dlls

     

    Erik

     

    Code Block

    using Microsoft.SqlServer.Management.Smo;

    using Microsoft.SqlServer.Management.Smo.SqlEnum;

     

    Server srv = new Server(".");

    foreach (Column col in srv.Databases["AdventureWorksDW"].Tables["DimProduct"].Columns)

    {

    Console.WriteLine("Col Name: {0} Col DataType: {1}", col.Name, col.DataType.SqlDataType.ToString());

    }

     

     

    Wednesday, December 12, 2007 11:24 PM

All replies

  • I'm not clear what you require. Your table should exist before you start the Data Flow. The Flat File connection defines the columns in the file, and ideally this will match the SQL table. If they do not match, you will need to transform the data.

    These two definitions are really required to start with. Do you have them both?

     

    Monday, December 10, 2007 3:40 PM
  • Yes. I was finally able to get it to work to a point where I can read the flatfile source and write rows into the destination table. But for this, I hard coded the source column names and changed the SQL server destination column size to default char(255) from original size char(50)

     

    Now, If I want to do following what do I need to do ?

    1. Define the source columns based on destination table schema. In other words, first check the destination column name, length, dataType etc. and then createSource columns accordingly.
    2. Ignore the identity column (or any other column that is not needed)

     

    Here's my code snippet:

     

    private void AddFlatFileSource()
    {
        // Add the component to the dataFlow metadata collection
        this.flatfileSource = this.dataFlow.ComponentMetaDataCollection.New();

        // Set the common properties
        this.flatfileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
        this.flatfileSource.Name = "FlatFileConnection";
        this.flatfileSource.Description = "Flat file source";

        // Create an instance of the component
        CManagedComponentWrapper inst = this.flatfileSource.Instantiate();
        inst.ProvideComponentProperties();

        // Associate the runtime ConnectionManager with the component
        this.flatfileSource.RuntimeConnectionCollection[0].ConnectionManagerID
     = this.package.Connections["FlatFileConnection"].ID;
        this.flatfileSource.RuntimeConnectionCollection[0].ConnectionManager
     = DtsConvert.ToConnectionManager90(
     this.package.Connections["FlatFileConnection"]);

        // Add columns to the FlatFileConnectionManager
        this.CreateSourceColumns();

        // Acquire the connection, reinitialize the metadata,
        // map the columns, then release the connection.
        inst.AcquireConnections(null);
        inst.ReinitializeMetaData();
        inst.ReleaseConnections();
    }

    ///////////////////////////////////////////////////////////////////////////

    private void CreateSourceColumns()
    {
        // Hardcode the columns for now
        srcColumns.Add("\"Column1\"");
        srcColumns.Add("\"Column2\"");

        // get the actual connection manager instance
        wrap.IDTSConnectionManagerFlatFile90 ff = null;
        foreach (ConnectionManager cm in this.package.Connections)
        {
          if (cm.Name == "FlatFileConnection")
              {
                 ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;
                    DtsConvert.ToConnectionManager90(cm);
              }
        }
        if (ff != null)
        {
          wrap.IDTSConnectionManagerFlatFileColumn90 column;
           wrap.IDTSName90 name;

     foreach (String colName in srcColumns)
     {
         // iterate
         // now create a new column for the connection manager
         column = ff.Columns.Add();
         // if this is the last row
         if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
      column.ColumnDelimiter = "\r\n";// add the row delimiter
         else
      column.ColumnDelimiter = ",";

         name = (wrap.IDTSName90)column;
         name.Name = colName.Replace("\"", "");
         column.TextQualified = true;
         column.ColumnType = "Delimited";
         column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
         column.ColumnWidth = 0;
         //column.MaximumWidth = 255;
         column.DataPrecision = 0;
         column.DataScale = 0;
     }
         }
    }

     


     

    Wednesday, December 12, 2007 6:11 PM
  • Try to use SMO against your table, with SMO you can obtain all metadata which you need from the database.

     

    Microsoft.SqlServer.Management.Smo

    Microsoft.SqlServer.ConnectionInfo

     

    and few others (Enum, Types, ...) namespaces|dlls

     

    Erik

     

    Code Block

    using Microsoft.SqlServer.Management.Smo;

    using Microsoft.SqlServer.Management.Smo.SqlEnum;

     

    Server srv = new Server(".");

    foreach (Column col in srv.Databases["AdventureWorksDW"].Tables["DimProduct"].Columns)

    {

    Console.WriteLine("Col Name: {0} Col DataType: {1}", col.Name, col.DataType.SqlDataType.ToString());

    }

     

     

    Wednesday, December 12, 2007 11:24 PM
  • Thanks. I will try this.

    Friday, December 14, 2007 6:32 PM