SSIS Script Task: Select from datatable1 into datatable2

Question

I'm using SQL 2014, Visual Studio 2013. I have the following code that's part of a larger script task:

//Load DataTable1 with Source Excel Sheet Data
OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn);
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
DataTable dt = new DataTable();
adp.Fill(dt);

What I would like to do next is select a targeted number of columns from datatable1, and fill another datatable. Something like this:

//Load another DataTable with Data from datatable1
OleDbCommand oconn2 = new OleDbCommand("select [column1], [column2] from datatable1", cnn);
OleDbDataAdapter adp2 = new OleDbDataAdapter(oconn2);
DataTable dt2 = new DataTable();
adp2.Fill(dt2);

Is this possible? The reason behind this is complicated, but I don't know another way around it. My source files are Excel. So the data needs to be brought into a datatable. Then the column names need to modified. Then a table can be made with the modified column names. But first, the data needs to be brought into a datatable as is, as I'm not sure I want to modify the source files.

Thank you.

Why do you need to modify column names? What exactly are you trying to achieve? — shree.pat18, Dec 13 '18 at 07:14
@shree: These source files are artifacts that come from a forensic application, and the column names - primarily dates - have time stamps (i.e., "UTC", "PST") in the name. There are other instances where there are extraneous characters in the column names. I would like to remove this and normalize these column names before creating tables in SQL. The end result is I have stored procedures that run off of these tables and normalizing the column names is essential. — Craig, Dec 13 '18 at 07:21
By timestamps you mean column names like "13-12-2018 00:00:00UTC" i.e. column names change every time? Also, do you create DB tables on the fly, or are those already existing? — shree.pat18, Dec 13 '18 at 07:25
Yes, the names appear something like: "File System Last Modified Date/Time - UTC+00:00 (M/d/yyyy)". The next file may read "File System Last Modified Date/Time - PST-08:00 (M/d/yyyy)". I'd like one field that reads: "File System Last Modified Date/Time". The DB's already exist, it's the tables that need to be created on the fly. Once a table is created, and the field set to what I want (normalized), then any subsequent data can be easily lined up and inserted. — Craig, Dec 13 '18 at 07:30
Hmm, in that case, you could rename columns within the first datatable itself. Take a look at this: https://stackoverflow.com/questions/6407239/how-to-change-the-datatable-column-name — shree.pat18, Dec 13 '18 at 07:34
Yes, but then I need to query that datatable for select columns using those new column names. Hence my dilemma. — Craig, Dec 13 '18 at 07:40
Sorry, can't seem to chat due to restrictions at work. But if you rename the columns, it should be straightforward to map to the target DB column names, unless there's some other step I'm missing in your workflow? — shree.pat18, Dec 13 '18 at 07:42
Define your data table and refer to your source using ordinals. This assumes your source has the same column structure. — KeithL, Dec 13 '18 at 14:08
Why don't you use the built-in tools to import data from your excel files and then map the the data to your destination? Barring that, why not split your script task up so that it populates an ADO.NET table that you can use in a Data Flow Task to insert into your destination. Then the rest of your script task could run after the DFT. — digital.aaron, Dec 13 '18 at 15:27
@digital.aaron: I will be creating a table with normalized columns. As I receive new files I will need to compare the fields of these new files to the fields of the table I created from the first file - the field names need to match up or the insert will fail. Also, I don't want to map data - the fields in these source files not only change but can also be in different order. I need this to be more dynamic. — Craig, Dec 13 '18 at 15:53
I think the solution could be simply this: I import the data into a datatable or, alternatively, an object variable/result set, from the source Excel file. Then modify the column names as needed. Then I need to refer to (select from) that datatable as my example shows above - I just don't know how to do that. — Craig, Dec 13 '18 at 15:57

score 0 · Accepted Answer · answered Dec 17 '18 at 17:53

Define the second DataTable using the data types and new names for the columns that will be added to it. Then you can populate this object by using the ordinal position of the columns in the initial DataTable with the Add method as below.

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("NewColumnName1", typeof(int));
            dt2.Columns.Add("NewColumnName2", typeof(string));

            //in this example dt is the original DataTable
            foreach (DataRow dr in dt.Rows)
            {
                          //add only necessary columns by their
                          //ordinal position in source DataTable
                         dt2.Rows.Add(dr[1], dr[0]);
            }

SSIS Script Task: Select from datatable1 into datatable2

1 Answers1