Trying to import excel data from several files using DTS

I am trying to use the followin script in a DTS package using the fileOject system to find all files in a folder and inport the excel data to a table.  but i am getting an error,

Here is the function i am using
Function Main()


      Dim oFSO
      Dim FileName
      Dim objFolder
      Dim objFile
      

      Set oFSO = CreateObject("Scripting.FileSystemObject")
      
      set objFolder = oFSO.GetFolder("C:\Documents and Settings\My Documents\OptOutProcessing")
      'set objFolder = oFSO.GetFolder("\\citscustmast\c$\OPTOUT\UPLOAD")
      
      for each objFile in objFolder.files
            If oFSO.FileExists(objFile) Then

                  ' *********************************Connection to CitsCustMast**********************************
            
                  set cn = CreateObject("ADODB.Connection")
                  cn.connectionstring = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=123;Password=123;Network Library=DBMSSOCN;Initial Catalog=Customer;Data Source=MAST"
                  cn.OPEN

                  'sqlInsert = "Insert into tblOptout ([Email Address], [Opt Out], [ID]) Select [Email Address], [Opt Out], [Survey ID] FROM objFolder.objFile.[Sheet$1]"
                  
                  sqlInsert = "Insert into tblOptout ([Email Address], [Opt Out], [ID]) SELECT  [Email Address], [Opt Out], [Survey ID]  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=objFolder.objFile', Sheet1$)"

                  cn.Execute (sqlInsert)

            End If
            
      next
      
      
Set oFSO = Nothing

      Main = DTSTaskExecResult_Success

End Function

and here is the error
 "ole DB Provider 'Microsoft.Jet.OLEDB.4.0' reported an error, the provider did not give any information about the error

Any assistance iin fixing this code will be appreciated

krbnldyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

udayshankarCommented:
check this out!!

http://www.sqldts.com/default.aspx?292

this website has all the info you need to import and export files in and out of sql server
krbnldyAuthor Commented:
I am afraid that this does not show me how to get an xls sheet from a file
nmcdermaidCommented:
Here's one problem:

'Excel 8.0;Database=objFolder.objFile',


It should be more lke this:


'Excel 8.0;Database=' & objFile.Path & objFile.Name,


Also:

-To simplify things, ensure your test source path doesn't have spaces.
-Keep in mind that this source path is on your SQL Server - its not on whatever machine is running the DTS.


To help with debugging, pop up a message box with your full insert statement in it too.

For example:

MsgBox sqlInsert


HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

nmcdermaidCommented:
For clarity, here is the full statement:

               sqlInsert = "Insert into tblOptout ([Email Address], [Opt Out], [ID]) SELECT  [Email Address], [Opt Out], [Survey ID]  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=' & objFile.Path, Sheet1$)"

MsgBox sqlInsert



(Note: I got it wrong last post - you just need objFile.Path)



Anthony PerkinsCommented:
Have you considered, using a connection object to your Excel spreadsheets and setting their names dynamically.  This way you can bypass all this ADO code and instead rely on DTS to import the data.  If you are interested this link shows how to get all the files in a folder:

Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
krbnldyAuthor Commented:
nmcdermaid

I tried implementing your code and it is giving me the error "Incorrect syntax near '&'.  When i tried to modify the code I come up with the same error as before
nmcdermaidCommented:
Try this:

               sqlInsert = "Insert into tblOptout ([Email Address], [Opt Out], [ID]) SELECT  [Email Address], [Opt Out], [Survey ID]  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" & objFile.Path & "', Sheet1$)"


I can't guarantee it'll work because unfortunately there's a lot of messing about with double and single quotes when you do it this way. An example connection string from www.connectionstrings.com is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""


What I suggest you do is try to get the syntax correct in Query analyzer first. Once you know that works you can try it as an ActiveX script.


Also acperkins makes a valid point. The method you are using doesn't leverage DTS at all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.