Updating a sql table from excel
Start the process at a time when you don't need immediate access to other files in Excel, and make sure you have adequate disk space to save the file again.
And if so, would there ever be updates, wouldn't it all just be inserts. Cells(l Row, 2) SQLSelect = "select ID from Test where Country= ' " & Col A & " ' and Name=' " & Col B & " ' and Month=' " & Split Month& " ' and Year=' " & Split Year & " ' " Cn. I am going to assume you know the basics of SSIS and so will start from the perpective of a new project in BIDS. Now that is in place, both streams of data need to be sorted by the ID columns (note, make sure you pass through the columns you have just converted and don’t pass through the Excel columns. We will perform the left outer join and the conditional to determine if each row is either a new row or existing row. Double click on the Merge Join, select the UID column from the database, and the UID, First Name, Surname, and Age columns from the Excel Source. Remeber to change the join type to Left outer Join Now we can perform the conditional split: 1.Now things get exciting, we want to create a data flow that queries both the Excel File and the database table, both will need to be sorted on the same column, and then we perform a left outer join. Configure the Excel Source to open our Excel file (you will need to create an Excel Connection manager) 3. Configure the OLE DB source to reference the person table (you will need to create an OLE DB Connection manager instance) 5. Double click the conditional split and configure as shown below: This will now give us two streams coming out of the conditional split, those belonging to the the New Row and those belonging to the Existing Row. Configure the OLD DB Destination to insert to the person table 3.Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Note: Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. From that i am able to read the current month and previous month sheets data and inserting those columns data into sql table.