December 23, 2008 at 8:38 am
I am new to SSIS and I have been given the task as below and I was given 2 days to finish:
I have scenario in this fashion:
Import Excel sheet data to SQL Server database:
-Create a Employee table with some structure
-Create employee_Modified table with the following two additional columns (along with the existing columns )
oCountry varchar (25)
oRegion varchar(25)
Step 2: Generate a script to identify whether the table is modified or not you need to write a routine i.e, to identify whether it is employee or Employee_Modified. If it is employee_modified then create a Alter table Script to apply on Production tables script.
Step 3: Execute the DDL script to create employee_temp table (if there are additional columns then it will be employee_modified).
PLease let me know as per the approach
December 23, 2008 at 7:15 pm
Can I just confirm what the underlying business requirement is here? Is it that there are constant amendments to an Excel spreadsheet of employee data and you want these amendments to be propagated to a SQL Server database?
What do Country and Region have to do with this? You have not described how they are populated.
An ALTER TABLE script is surely not what you want? If this is just an import / upsert / archive process, you should not need to execute any DDL, IMO.
Phil
December 26, 2008 at 10:01 am
Hi Phil,
Exact Reqt is :
-Create a Contact table with the same structure/ columns mentioned in the excel dotnet_consumer_workbook_v_6_0
-Create Contact_Modified table with the following two additional columns (along with the existing columns )
oCountry varchar (25)
oRegion varchar(25)
Step 2: Production table DDL script is similar to Contact table DDL script. But to identify whether the table is modified or not you need to write a routine i.e, to identify whether it is Contact or Contact_Modified. If it is Contact_modified then create a Alter table Script to apply on Production tables script
Please help me in getting the script code for this logic
December 26, 2008 at 10:53 am
The client will be updating the new columns in the excel sheet with updated columns. so the excel sheet will be always updated frequently and so shall be updated.
Main task for me is to write a script to identify the identify between master and updated table as to which contains the updated data ; and so on and after identifying making updates to the temporary excel table with the latest updated data
December 26, 2008 at 7:54 pm
The ALTER TABLE requirement continues to puzzle me. Anyhow, now that I understand better what you are trying to achieve, here is how I would do it:
1) Create a staging table in SQL Server that includes the structure of the Excel sheet (and the two new columns, if needed - you still have not mentioned where the data is coming from for these).
2) Add a binary checksum field to the staging table.
3) Create your SSIS routine - skeleton process:
a) Remove data from staging table
b) Import all data from Excel
c) Populate the binary checksum
d) Perform an UPDATE from the staging table to the production table for all records where there is a checksum discrepancy
e) Perform an INSERT from the staging table to the production table where a new record has been created on the spreadsheet (I am assuming that there is some sort of PK on the spreadsheet which will allow you to easily identify any new records?)
Note: this method does not accommodate deletions.
Phil
December 29, 2008 at 3:11 am
Phil,
The additional columns will be filled by the client itself in the modified excel sheet with updated data manually.
December 30, 2008 at 12:09 pm
Phil,
As I am a new comer, can you please tell me what I need to do with respect to SSIS tool
It could be very helpful if you can give me detailed explanation as to what you mean..
Please clearly elaborate on this so that I can understand step-by-step process.
Which scripting I need to use to acchieve this?
Which tasks I need to use from BIDS?
Please tell me detailed manner. I would be grateful to you if you can do this ASAP
Srini
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply