Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

After spending some time trying to load data using a C# ETL loader we created, I noticed that SAP exposes a "Bulk load" option in their ado.net driver. I couldn't find any examples on using it, so I figured I would post an example. Performance is pretty good, with the BatchSize = 1000, I am able to load 1 million records in about 10 seconds.


        private string dbConnectString;
        private HanaCommand dbConnection;
        public void LoadData(DataTable dt, string tablename, string[] columns)
        {
            string[] fullName = tablename.Split('.');
            dbConnectString = ConfigurationManager.AppSettings["DatabaseString"];
            HanaConnection dbConnection = new HanaConnection(dbConnectString);
         
            dbConnection.Open();
            HanaCommand da = new HanaCommand("SET SCHEMA " + fullName[0], dbConnection);
            da.ExecuteNonQuery();     
          
          
            HanaBulkCopy blkcmd = new HanaBulkCopy(dbConnection);
            blkcmd.BulkCopyTimeout = 1000;
            blkcmd.DestinationTableName = fullName[1];
            HanaBulkCopyColumnMappingCollection mappings = blkcmd.ColumnMappings;
            for (int i = 0; i < columns.Length; i++)
            {
                mappings.Add(new HanaBulkCopyColumnMapping(i, columns[i]));
            }
            blkcmd.BatchSize = 1000;
            blkcmd.WriteToServer(dt);
            blkcmd.Close();
            dbConnection.Close();
            blkcmd.Dispose();
            dbConnection.Dispose();
        }

A few notes about implementation and some things I found:

1.The datatable has to have matching datatypes for the columns, but the rows can be all strings, so when you initially build your datatable you have to do something like this for each column:


DataTable dt = new DataTable();                                       
DataColumn dc = new DataColumn("col" + j.ToString(), typeof(double));
dt.Columns.Add(dc);

And specify if it is a double, string or DateTime etc.

2.The BatchSize has some strange behavior. I didn't play with it much, but if you don't define it then performance is really terrible. Not any better than inserting one record at a time. I found 1000 gives great performance, but I bet it could be even better, so if someone is able to do some metrics that would be great.

3. I am using the HanaBulkCopyColumnMapping in my example, but it is completely optional to use. If you don't provide the mapping, then column 0 in the datatable gets mapped to column 0 in Hana 1:1, 2:2 etc. The mapping is nice of course, because then you don't have to have the same number of columns or the correct order, I just pass the tables columns names that I am loading in as an array string in the example.

4. You will notice that I switch the Schema before loading. If you don't do that, then even if you pass in the fully qualified name (ex USERS.USER) the bulk loader will still try to load into its own schema.

5. The connection string is a pretty simple string that consists of something like this:

"Server=hana-server:30015;UserID=username;Password=passcode"

4 Comments
Labels in this area