SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS


SSIS

Author
Message
wanox
wanox
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 226
Can anyone please tell how to import data from an excel file to sql server table in SSIS using a stored procedure. I have an excel connection manager which is used by an excel source in the data flow. I don't know where to go from here. Thanks!!
Henrico Bekker
Henrico Bekker
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7488 Visits: 5012
create procedure _sp_importexceldata           
(@Source varchar(1000)
, @SourceSheet varchar (100)
, @DestinationTable varchar (100))
as

declare @retval int
EXEC master..xp_fileexist @Source, @retval output

if @retval = 0
begin
print 'file does not exist.'
return
end

if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]'
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) +
convert(varchar, getdate(), 126)

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + @SourceSheet + ')')



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62159 Visits: 13297
Just a small remark: Henrico's SP is great, but you cannot use it in the Excel Source with an Excel connection manager. You need to launch the sp from an Execute SQL Task with a connection manager pointing to your DB.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34467 Visits: 16649
If you already have an Excel connection manager and a data flow, you don't need to use a stored procedure - not to do the actual importing, anyway. What are you trying to do - move everything from an Excel workbook into a table with the same column structure?

John
wanox
wanox
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 226
Thank you guys for responding.

The spreadsheet has 160 columns with one row for each column. The destination table has a primary key called "ID" which is not on the spreadsheet.
The 1st step is to get the "ID" from another table where the columnname matches the columnname on the spreadsheet.
The 2nd step is to insert the "ID" and the value of each column to the destination table.

How can I achieve this?

Thank you!
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62159 Visits: 13297
You can use the Excel Source in the data flow to read the Excel file (not with a stored procedure). Then you can use a Lookup component to find the corresponding ID and insert it into the destination with an OLE DB Destination.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
wanox
wanox
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 226
This will not work because the excel column header is a row in the table. For instance, the 160 column header is the same as the 160 rows in "Name" Column of the table. I will have to do some pivoting but I am not sure where this will take place
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62159 Visits: 13297
Can you give some sample data so we can see what we're dealing with?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
wanox
wanox
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 226
File sample data:

col1 col2 col3 col4 COL5
1.22 1.2475 1.395 1.6 1.275

The file will always have two rows with 160 columns.

The column names in the file is = to the result of this query: Select Name from table where id = 2

The lookup might only work if I can find a way to transpose the columns to rows and give the column name "Name"
wanox
wanox
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 226
Koen, is this data sufficient?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search