SSIS - Import Excel files into SQL

Question

SSIS - Import Excel files into SQL

onebite2

Valued Member

Points: 64
More actions
December 2, 2011 at 10:08 am

#391019

SSIS process
1. ForEachLoop container
2. Execute Sql task within the container.
3. Execute Stored Proc, which would import the *.xls file into sql.
Package level Variable: @FileName
Getting errors executing the package.
[Execute SQL Task] Error: Executing the query "Execute ImportExcelIntoSQL ?"
failed with the following error: "Parameter name is unrecognized.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Look at the attachment for more information.
Stored Procedure:
Create Procedure ImportExcelIntoSQL
@FileName varchar(30)
AS
Set NoCount ON
DECLARE @sqlStr VARCHAR(4000);
Set @sqlStr='select * into ' + @FileName + ' FROM
OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=C:\ExcelFiles\' + @FileName + ''', [Sheet1$])'
exec(@sqlStr)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

onebite2 (12/2/2011)
Set @sqlStr='select * into ' + @FileName + ' FROM
OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=C:\ExcelFiles\' + @FileName + ''', [Sheet1$])'
exec(@sqlStr)

A couple things to mention here:

1. Have you attempted to execute the proc separately? Does it work? You are using the @FileName parameter as the reference to your source and destination table...that doesn't seem right.

2. Is there a reason why you are doing it this way in a stored proc, as opposed to using a dataflow with an Excel destination?

onebite2 Valued Member Points: 64 More actions · Answer 2

Here is my task.

1. I have some excel files in a folder(each file has different columns).

2. Files are not the same all the time.

3. I need to import files into SQL.

This is the only way i understood i can do this because of the dynamic nature.

Tables need to be created dynamic with data.

If there could be different of way doing this, Pls. educate me 🙂

Thanks!!

onebite2 Valued Member Points: 64 More actions · Answer 3

Yes. If i pass the actual filename to variable @FileName directly, everything rocks!!

Problem is only if i try to automate the process.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 4

onebite2 (12/2/2011)
Here is my task.
1. I have some excel files in a folder(each file has different columns).
2. Files are not the same all the time.
3. I need to import files into SQL.
This is the only way i understood i can do this because of the dynamic nature.
Tables need to be created dynamic with data.
If there could be different of way doing this, Pls. educate me 🙂
Thanks!!

Ok, it makes sense that you want to do it like this then.

My reason for disputing the use of the parameter is because surely your parameter has a ".xls" at the end...and I'm not sure if you want to create a table with that name. If you are passing the filename without the extension and it works, then disregard this point.

Personally, I would build up the procedure call with an expression rather than by referencing the parameter with a "?" in the call. I have seen many issues with the way in which SSIS sometimes try to parse stored proc calls when parameters are passed like this...so give it a try and see if that works better.

onebite2 Valued Member Points: 64 More actions · Answer 5

Ok, it makes sense that you want to do it like this then.

My reason for disputing the use of the parameter is because surely your parameter has a ".xls" at the end...and I'm not sure if you want to create a table with that name. If you are passing the filename without the extension and it works, then disregard this point.

Personally, I would build up the procedure call with an expression rather than by referencing the parameter with a "?" in the call. I have seen many issues with the way in which SSIS sometimes try to parse stored proc calls when parameters are passed like this...so give it a try and see if that works better.

1. Parameter @filename should have filename only. Take a look at the attachment. I clearly chose filename only.

2. Thing is, I don't know how to use expression. Could you send me a syntax.

3. My error message(from original issue), don't have to do anything with parameter??? I'm not sure though. What are your thoughts on error message i get.

Thanks!!

onebite2 Valued Member Points: 64 More actions · Answer 6

Oops, i made change to parameter passing and now i get different error.

Here is the error message:

[Execute SQL Task] Error: Executing the query "Execute ImportExcelIntoSQL ?"

failed with the following error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

does not contain the table "Sheet1$". The table either does not exist or the current user does not

have permissions on that table.". Possible failure reasons: Problems with the query, "ResultSet" property

not set correctly, parameters not set correctly, or connection not established correctly.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 7

I think the issue could be how SSIS passes that parameter...

Use the expression builder in the Execute SQL Task properties to set the "SqlStatementSource" propery of the task.

The expression should look something like this, and you can you the "Evaluate Expression" feature button to show what the end result will look like:

"exec ImportExcelIntoSQL '" + @[User::FileName] + "'"

Remember the single quotes...

onebite2 Valued Member Points: 64 More actions · Answer 8

Thanks a lot for your help.

I think i found the solution.

Rather strange.

Only some files have Sheet1 at the bottom.

I have made changes to all files to make sure they all have Sheet1.

After the change i was able to execute my package with no problem.