April 26, 2012 at 3:55 am
Hi ;
1. I am new in SSIS , i am using SQLSERVER 2005 BI , My scenario load the data into warehouse if it does not exist; if the data exist then update the latest data given the excel source , based on the certain key fields, when the second scenario the data does not exist "INSERT" it and when data Exist "UPDATE " it. The entire job is run the single SSIS package . and formatting the excel data in propercase .(IN THIS CASE TABLES ARE PREVIOUSLY IN THE DATABASE WE NEED TO COMPARE THE DATA AND UPDATE THE LATEST DATA AND TO INSERT THE NEW DATA)
2. Excel sheet contains multiple sheets and some time multiple excel sheet are give . we need to create a dynamic excel connection to connect the oledb destination. each excel sheet create a new table in oledb destination based on the excel sheet name.
(IN THIS CASE WE HAVE TO CREATE THE TABLES IN DATABASE BASED ON THE EXCEL SHEET
How can i do this any help would be appreciated .
Thanks;
FA-DE
April 26, 2012 at 10:52 am
I think you will need to do all of this in a Script Task using the Excel Object Model to inspect each Excel Workbook to iterate over the Worksheet collection, getting the structure of each, creating the database table from it, and then bulk loading the data after iterating over the rows in each Worksheet.
I do not know if SSIS is the right tool for you. It sounds like a C# Console Application using the Excel Object Model, ADO.NET SqlCommand and the SqlBulkCopy class would be a better vehicle.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply