We are getting the CSV File which column and data are separated with any wild card character like !,@,#,$,%,^,&,*,?,\,/ etc.
See the files below.
In health
care domain some time we are getting such type of flies and we need to load
this file to the database.
We are
loading the flat file or CSV files in the database using the flat file connection
manager. But this source has some limitations.
Here we are
not getting the * delimiter.
For loading such
type of file either we need to use Script task or script component
transformation.
Let’s in In this demo we will see who we load this file using Script task and the next post
we will see how we load this same file using the script component task.
Now taking script task
We are
writing the below code
|
public void Main() { try { // TODO: Add your code here char[] delimiters = new char[] { '*' }; //CSV file with * seperated DataTable dt = new DataTable(); using (StreamReader reader = new StreamReader("H:\\SSIS1\\Source\\Sales.csv")) //Reading the CSV file { string[] headers =
reader.ReadLine().Split('*'); // Adding the Header into
the data table foreach (string header in headers) {
dt.Columns.Add(header); } dt.Columns.Add("Created_Date"); //Adding Created_Date column in the header dt.Columns.Add("Created_by"); // Adding created_by column in the header while (!reader.EndOfStream) { string[] rows = reader.ReadLine().Split('*'); DataRow dr = dt.NewRow(); for (int i = 0; i <
headers.Length; i++) { dr[i] = rows[i]; } dr["Created_date"] = DateTime.Now; //Adding Created_Date dr["created_by"] = "Bagesh"; //Adding Created_by dt.Rows.Add(dr); } } // creating connection to the data base string connection = "Data
Source=BAGESH-PC\\BAGESHDB;database=Test;integrated Security=SSPI;"; SqlConnection con = new SqlConnection(connection); //create object of
SqlBulkCopy which help to insert SqlBulkCopy obj = new SqlBulkCopy(con); //assign Destination table
name obj.DestinationTableName = "sales_Stg"; //Mapping the Source column
with destination column. //Keep in mind we need to
provide the correct mapping otherwise it will throw an error obj.ColumnMappings.Add("SalesOrderNumber", "SalesOrderNumber"); obj.ColumnMappings.Add("SalesAmount", "SalesAmount"); obj.ColumnMappings.Add("UnitPrice", "UnitPrice"); obj.ColumnMappings.Add("ExtendedAmount", "ExtendedAmount"); obj.ColumnMappings.Add("TaxAmt", "TaxAmt"); obj.ColumnMappings.Add("Created_Date", "Created_Date"); obj.ColumnMappings.Add("created_by", "created_by"); //Opining the connection con.Open(); //insert bulk Records into
DataBase. obj.WriteToServer(dt); //Closing the conntion con.Close(); Dts.TaskResult = (int)ScriptResults.Success; } //If any error comes loging
that error catch (Exception ex) { Dts.Events.FireError(0, "Fire Error", "An error occurred:
" +
ex.Message.ToString(), "", 0); Dts.TaskResult = (int)ScriptResults.Failure; } } |
Build and
close the window.
Our package
is ready to run.
Before run
see the records in the table.
Now loading
the file into the database.
Package
executed successfully.
See the records in the table.
Data loaded successfully.
