Link to home
Start Free TrialLog in
Avatar of harveystephenb
harveystephenbFlag for United States of America

asked on

SSIS Package to Update table from Excel Spreadsheet

I wish to develop an SSIS Package to Update an SQL table from  an Excel Spreadsheet.

I am using the BI Studio for SQL 2005.

The spreadsheet would include a column with the table's primary key and several other columns with data to be updated in the sql table row having the same primary key value.

Can anyone suggest an approach in terms of which components to use which are available in the BI Studio?
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Add a dataflow to your control flow, and inside dataflow add:
1. An Excel Source and configure it to get data from excel
2. An OLEDB Command and configure it with an UPDATE statment to update the destination SQL table based on the ID that exist in source and destination... Link the source to destination

Helped?
regards,.
Pedro
Avatar of harveystephenb

ASKER

That was my first guess in terms of components.  What is not clear to me is how to reference the Excel spreadsheet columns as the source for values in my update query which is located in the ole db desitination editor.
Use OLEDB Command, and I suggest using a stored procedure in database and add a statment likke:
EXEC MyStoredProcedute ?,?,?

Helped?
But  how tdo I reference the Excel spreadsheet columns as the source for values in my update query regardless of where the query is located?
Could you attach your excel file?

What you mean by reference Excel spreasheet columns?! When you link the soruce to the OLEDB command component you get it!
This is the excel file.  The SQL table I am trying to update looks like:

USE [LWR_new]
GO
/****** Object:  Table [dbo].[T_LWR]    Script Date: 12/04/2009 11:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_LWR](
      [LWR_No] [int] NOT NULL,
      [Status_ID] [int] NULL,
      [Date] [datetime] NULL,
      [Due_Date] [datetime] NULL,
      [Close_Date] [datetime] NULL,
      [RequestedBy_ID] [int] NULL,
      [Engineer_ID] [int] NULL,
      [Technician_ID] [int] NULL,
      [Customer_Num] [nvarchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Family] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Model_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Part_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BOM] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Subject] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_T_LWR] PRIMARY KEY CLUSTERED
(
      [LWR_No] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
test.xls
dear Friend,
The logis is like the attached image.
If you have difficult I can do the example for you.
Helded?
regards.
Pedro
SSIS-Interface.JPG
I have difficulty.  How do I reference the Excel column name in the SQL UPDATE.  I tried something like this which expresses my intent.  The database already contains the rows I am updating.  I am using the excel spreadsheet as a transaction file for updates.

update t_lwr set model_number=model# where lwr_no=lwr#

model_number is column name in SQL table.  Model# is column name in Excel spreadsheet.

lwr_no is column name and primary key for existing row in the database table.

lwr# is the column name in the Excel spreadsheet.

Could you do an example?
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your help!