Insert excel to SQL DB

Question

Insert excel to SQL DB

Susan S

SSCommitted

Points: 1771
More actions
January 13, 2008 at 10:12 pm

#123114

HI,
I am new in SSIS.
I have a task to import excel file to SQL DB automatically ( monthly) then create a report.
I am not really sure how do I import an excel file to SQL.
what I have done is to create SSIS.
1. create a bulk insert task to control flow
2. excell source
3. ole DB destination source
once I run it. I just don't know what I am doing wrong.
Please help me? or any other suggestion ?
thanks,
Susan
there are error messages as below:
Warning: 0x800470C8 at Data Flow Task, OLE DB Destination [130]: The external metadata column collection is out of synchronization with the data source columns. The column "InvoiceNo" needs to be updated in the external metadata column collection.
The column "Location" needs to be updated in the external metadata column collection.
The column "LocName" needs to be updated in the external metadata column collection.
Error: 0xC002F304 at Caltext Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot bulk load because the file "\\msfile01\IT-Dept\Operations\IT - Projects\2008\OZC08008 - Caltex Reporting\Caltex September detail.xls" could not be opened. Operating system error code 5(error not found).".
Task failed: Caltext Insert Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
The program '[5976] Package.dtsx: DTS' has exited with code 0 (0x0).

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

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

Animal Magic SSChampion Points: 13964 More actions · Answer 1

Does it fail when you run it as yourself or when it runs as the agent? If its when the agent runs it as a job make sure that the sql agent service account has access to the file path.

Also check that the file is not open anywhere else.

You may also need to remap your columns in your data flow step as it looks like its having issues there too.

Susan S SSCommitted Points: 1771 More actions · Answer 2

Yes I run it via VS when the errors appear

I am not really sure why it can't open the file, I have permission to that folder too.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Dumb question... are you trying to test the code while the spreadsheet is open? If so, BOOOM! You'll get the error you got.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Susan S SSCommitted Points: 1771 More actions · Answer 4

the file doesn't open.

anyway - I solve it now by running import/export wizard through management studio.

Thanks for that.

and now I am still looking some info - how to schedule this so it can automatically import

can you help me on this

cheers,

Susan

jona-913705 SSC Enthusiast Points: 145 More actions · Answer 5

Don't know if this has been answered elsewhere, links anyone?

But I'm in the same boat, I've created a macro in excel which automatically formats and saves a file in the same location and I want SQL server to auto import (append) to an existing table everyday, I've only really done the reporting side so I haven't a clue where to start looking or what even it might be called so it's difficult to even search for it on here - If someone could just give a little clue as to what technique I need to use so at least I can start searching the help and the forums and my books!

I don't whether I need a trigger or a stored procedure or a function or something else???

ANY clues greatly appreciated.

Animal Magic SSChampion Points: 13964 More actions · Answer 6

Hi Jona,

you probably want to be looking at an SSIS package using a data flow task with an Excel source and OLE DB sql server destination. That way you can schedule the package to read in the contents of your file on whatever basis you need it too. All that is really involved is mapping the excel columns to your table design in the database.

Also if your filename changes you will need to use a variable (if you know what the filename is going to be) as your source connection. if you dont know what the filename is going to be you can use a for each loop container to read in any files in a certain directory, ask about that here if it comes to this and i/someone else will help

jona-913705 SSC Enthusiast Points: 145 More actions · Answer 7

Hey thanks a lot Animal Magic, I've got the package working and it tested fine. The only problem I have now is when I try to save the SQL Server Agent Job I get this error message

TITLE: Microsoft SQL Server Management Studio

------------------------------

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)

------------------------------

BUTTONS:

OK

------------------------------

Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)

at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)

at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()

at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

Animal Magic SSChampion Points: 13964 More actions · Answer 8

ok im not 100% what the issue is but the error seems to mention keys. go back to your SSIS package in BIDS and left click anywhere on the control flow and take a look at the properties of the package (bottom right by default). there is a "security" secion, change the protection level to be EncryptAllWithPassword, click in the package password and press the 3 little dots, enter a password.

save package, redeploy to the server. Go back to your job and add the package in again, this time it should ask you for a password. put the password in you just created and press ok.

See if that gives you the same problem. Im going along the lines of it being encrypted using the user key and the sql agent runs under a user that isnt you. i could be on the completely wrong track though 🙂