Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Westside
Starting Member
9 Posts |
 Posted - 2009-02-16 : 18:38:22
|
| Hi, I am relatively new to SSIS and I have a recurring need to import an excel file into a SQL Server 2005 database. The excel file contains data that ultimately needs to be stored in multiple tables. I am looking to find out how I would design a package that would do this for me. I don't have much control on how I get this data from a formatting/structure standpoint.Basically users are taking a test. Each column header represents a "question_id" on the test. The "email" column in the spreadsheet is the user's email address. The values are their responses to each question. The responses can be a number between 1 and 5. I would like the package to essentially do:1. Determine if the user has a record in the "users" table, if not proceed to step 22. Create a record in the "users" table OR get the primary key using the email address from the spreadsheet if they have a record in the "users' table already3. Grab the ID(primary key) of the user we just entered so we can tie the answers to the right user4. Insert the values into the "Answers" tableThere may be a data conversion step in here because I have seen some issue with excel and unicode vs. non-unicode. My db tables are using varchar not "nvarchar" as I don't need nvarchar for this project.Column Headers (each number represents a question_id, and the email column is the user's email address ) 1 | 2 | 3 | 4 | 5 | 6 |7 | 8 | 9 | 10 | 11 | 12 | EmailSample Values3 | 5 | 3 | 5 | 5 | 4 | 3 | 2 | 5 | 5 | 4 | 1 | johndoe@test.com4 | 4 | 2 | 4 | 5 | 2 | 1 | 2 | 5 | 3 | 4 | 2 | roger@abccompany.com -----------------------Any help appreciated...Thanks,-Westside |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-17 : 14:06:34
|
| I suggest this, Upload your excel to a staging table with the same structure as your main table.The have a SQL task and run the two queries as below.DELETE FROM Main_table WHERE EXISTS (SELECT * FROM Main_table A JOIN Staging_Table B ON B.EMAIL_ID = A.EMAIL_ID)INSERT INTO Main_Table SELECT * FROM Staging_Table |
 |
|
|
kap_gemini
Starting Member
5 Posts |
Posted - 2009-02-17 : 15:47:23
|
| You need to do the lookup to get the UserID column, if exists (you work with green arrow portion) else create a new record in the User table. Once you have userID you can tied the result with other tableKapil Gupta |
|
|
|
|
|
|
Import Excel File and SSIS