ssis script task add filename and sheetname to the table

Question

ssis script task add filename and sheetname to the table

Dinesh Kalva 100

Hi, I'm using sqlbulkcopy dumping data into table. I want to add filename, sheetname to the table. Filename and sheetname fields are located last in the table. Below is my code.

FileInfo[] files = directory.GetFiles("*.*, SearchOption.AllDirectories);

foreach (FileInfo file in files)
{
string directoryname =  file.Directory>name;
string filename = file.Name;

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFolderPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""; 
 
OleDbConnection cnn = new OleDbConnection(connectionString);
cnn.Opne();

DataTable dtsheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow drSheet in dtSheet.Rows)
{
  if (drSheet["TABLE_NAME"].ToString().Contains("xlnm"))
	sheetname = drSheet["TABLE_NAME"].ToString();
	
	OleDbCommand con = new OleDbCommand("select * from [" +sheetname+"]", cnn);
	OleDbDataAdapter adp = new OleDbDataAdapter(con);

	con.CommandTimeout = 0;
	
	using (OleDbDataReader reader =  con.ExecuteReader()
	{
		using (SqlBulkCopy dbc = new SqlBulkCopy(myadoconn)
		{
			dbc.Batchsize = 5000;
			dbc.DestinationTableName = "Table1";
			dbc.WriteToServer(reader);
		}
	}
}

Accepted answer

0 additional answers

Your answer

Answer 1

Shivam Kumar 541

Hi @Dinesh Kalva
I guess changing the query from

"select * from [" +sheetname+"]"

to

"select *,"+filename+","+sheetname+" from [" +sheetname+"]"

should work as filename is the variable you are storing the filename [string filename = file.Name;] and sheetname is the one which is holding the sheetname [sheetname = drSheet["TABLE_NAME"].ToString();]

Assuming that your table has filename column before sheetname.

Dinesh Kalva 100 Reputation points

2023-08-20T16:23:16.5433333+00:00

I'm getting syntax error at filename
Shivam Kumar 541 Reputation points

2023-08-20T16:33:57.81+00:00
what's the exact name of the columns in the table ?

Use the alias for getting the resultset with the columns to match the table column names, there may be some concatenation issue, missing space in query perhaps, you can try building the query in a sepearte variable print it and then pass variable to OleDbCommand() to be sure that query is building correctly may be,

"select *,"+filename+" as FilenameColumnName,"+sheetname+" as SheetnameColumnName from [" +sheetname+"]"

what is the error ?

Dinesh Kalva 100

Here is the table structure....

create table table1 (filename varchar(10), sheetname varchar(10), column1 varchar(10),column2 varchar(10), column3 varchar(10),....column200 varchar(10)

Shivam Kumar 541 Reputation points

2023-08-20T17:01:33.66+00:00

In your table filename and sheetname are coming before all other columns, try modifying the sql query accordingly.

Dinesh Kalva 100

Hi, i have done small changes as per requirement...

I want to insert header row into this table: headertable1 and other data into other table: datatable1

FileInfo[] files = directory.GetFiles("*.*, SearchOption.AllDirectories);

foreach (FileInfo file in files)
{
string directoryname =  file.Directory>name;
string filename = file.Name;

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFolderPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""; 
 
OleDbConnection cnn = new OleDbConnection(connectionString);
cnn.Opne();

DataTable dtsheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow drSheet in dtSheet.Rows)
{
  if (drSheet["TABLE_NAME"].ToString().Contains("xlnm"))
	sheetname = drSheet["TABLE_NAME"].ToString();
	
	OleDbCommand con = new OleDbCommand("select *,'"+filename+"' as filename,'"+sheetname+"' as sheetname,'"+ from [" +sheetname+"]", cnn);
	OleDbDataAdapter adp = new OleDbDataAdapter(con);

	con.CommandTimeout = 0;
	
	using (OleDbDataReader headerreader =  con.ExecuteReader(CommandBehaviour.SchemaOnly))
	{
DataTable schemaTable = headerreader.GetSchemaTable();
List<string> columnNames = new List<string>();

foreach(DataRow row in schemaTable.Rows)
{
	string columnName = row ["ColumnName"].ToString()
	columnNames.Add(columnName);
}
		using (SqlBulkCopy headbc = new SqlBulkCopy(myadoconn)
		{
			headbc.DestinationTableName = "HeaderTable1";
			headbc.WriteToServer(headerreader);
			headbc.Close();
		}
	}
using (OleDbDataReader datareader =  con.ExecuteReader())  	
{
using (SqlBulkCopy dbc = new SqlBulkCopy(myadoconn)  		
{  			dbc.DestinationTableName = "DataTable1";  						  dbc.WriteToServer(datareader);  			
dbc.Close();  		
}
}

Share via

ssis script task add filename and sheetname to the table

0 additional answers

Your answer