Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 9:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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!!
Post #1477333
Posted Wednesday, July 24, 2013 11:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:05 AM
Points: 1,904, Visits: 2,842
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 + ')')



----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1477339
Posted Thursday, July 25, 2013 1:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 13,568, Visits: 10,444
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477359
Posted Thursday, July 25, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 5,308, Visits: 9,696
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
Post #1477367
Posted Thursday, July 25, 2013 8:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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!
Post #1477837
Posted Friday, July 26, 2013 12:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 13,568, Visits: 10,444
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477877
Posted Friday, July 26, 2013 1:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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
Post #1477881
Posted Friday, July 26, 2013 1:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 13,568, Visits: 10,444
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477884
Posted Friday, July 26, 2013 1:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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"
Post #1477886
Posted Friday, July 26, 2013 1:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
Koen, is this data sufficient?
Post #1477891
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse