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 ...

DBMS

Database : database can be defined as a collection of information stored at a particular place written in a predetermined manner, belongs to a particular topic. DBMS: (DATA BASE MANAGEMENT SYSTEM): it is defined as software which is present inside the database which is used o maintain or manage the data within the database . In the above diagram, if the user needs any info from the database. He has to contact with dbms , then the dbms goes to the database, takes the information from the database and gives it back to the user. Database models : these models are broadly divided into the following types: 1. FMS/FDMS 2. HMS/HDMS 3. NDBMS 4. RDBMS 5. ...

About Me

I am a Data Analyst /Azure 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 Azure SQL server, Power bi / Tableau ,Azure Data Factory, Azure SQL, T-SQL, Blob Storages as well as XML and working on Development and Deployment. My Technical skills are Azure SQL, Power Bi, Tableau ADLS, Azure Data Factory, SSIS, SSRS. Keywords Relevant to me : • Business Intelligence Developer • Data warehouse Developer • Data Analyst • Azure 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