How to skip rows in Excel and then copy the data in the SQL Server table.

There are mainly 3 ways in which we can skip particular rows in excel and then copy the data in the SQL server.

I have explained 2 ways in this Blog and the third method is by using a Script task, which is presently out of scope for this article.

Method 1 : In the first method , you can configure the openrowset property by right clicking on the Execute Sql task and then opening the properties > OpenRowSet .

Preview :

Method 2 : You can configure the Excel task , but in that choose Sql Command in the Excel connection manager and then type the following. This will solve the issue and also in this method you can rename the column name in the destination table as well.

How to remove special characters in the Data(using Script component Task and Regex)

Start with the Below screen shot ( Before Script Task) and you will come to know about the senario. Note : The Script Component also works for the special characters within the Data as well . for ex "A$hish" Hover over the below Regex. it will show > Show potential fixes > in that Select System.Text.RegularExpressions. The Below will disappear. Use the Below Script. public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.CustomerID = RemoveSpecialCharacters(Row.CustomerID); Row.NameStyle = RemoveSpecialCharacters(Row.NameStyle); Row.Title = RemoveSpecialCharacters(Row.Title); Row.FirstName = RemoveSpecialCharacters(Row.FirstName); Row.LastName = RemoveSpecialCharacters(Row.LastName); Row.CompanyName = RemoveSpecialCharacters(Row.CompanyName); Row.SalesPerson = RemoveSpecialCharacters(Row.SalesPerson); Row.EmailAddress = RemoveSpecialCharacters(Row.EmailAddress); Row.Phone = RemoveSpecialCharacters(Row.Phone); } public static string

How to Skip first n rows in the Flat File Source and move the data into the SQl Server.

Skipping of rows from the TSV file can very easily done by just mentioning in a Text box which is present in the Flat File configuration wizard. Select the no of rows you want to skip and you can see the output in the preview option. You can also do the same setting by clicking on the flat file connection manager and changing the value in the highlighted field.

About Me

I am a Azure Data Engineer/ Data Engineer with more than 4 years of full-time work experience and around 3.4+ years of experience in different Microsoft Technologies ( Azure Data Factory ,Azure SQL, Azure Data lake Gen 2). I currently work mainly with Azrue Data Factory, Azure SQL, T-SQL, Blob Storages as well as XML and working on Development and Deployment. My Technical skills are Azure Data Factory, Azure SQL, ADLS, SSIS, SSRS. Keywords Relevant to me : • ETL/ELT • Business Intelligence • Data warehouse • Data engineer I like Learning new Tech, so in free time i explore learning about tech , taking courses on Digital Platforms and attending online workshops. I like Singing and Cycling as my hobby. If you want to get in touch, find me at 'Gauravthamada gmail com'

Data With Gaurav

Search This Blog