harveystephenb
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?
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?
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?
EXEC MyStoredProcedute ?,?,?
Helped?
ASKER
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!
What you mean by reference Excel spreasheet columns?! When you link the soruce to the OLEDB command component you get it!
ASKER
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
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_
[Family] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_
[Model_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[Part_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[BOM] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[Subject] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_
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
The logis is like the attached image.
If you have difficult I can do the example for you.
Helded?
regards.
Pedro
SSIS-Interface.JPG
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help!
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