Friday, February 24, 2012

DTS is crazy! or maybe its me?

Hey all,
I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.
Am I missing something here?
Here is the SPROC:
CREATE PROCEDURE [dbo].[importEmployees] AS


DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, 'C:\importEmployees.dts', ''
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

GO

Ok, so it was me... I had the datatype wrong on the excel file which was set to numeric and I put in text.

No comments:

Post a Comment