OPENROWSET - last column not returned unless excel file manually saved

I have an excel file residing on the same server as the sql server and I am trying to insert the contents of this file into an sql server table. The issue is the following. When I try to use OPENROWSET method to return records from the file, it returns data for all columns but the last column. The last column and the data somehow is not returned. However, when I manually open the excel file and save it atleast once, everythink works as expected, i.e now after manually saving the excel file, the last column and the data is also returned . Please see the code section and let me know if there is any specific reason for this issue:

Thanks


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Matrix Data Feed\AccVendorMappingSource.xls', 'SELECT * FROM [VendorMapping$]')

Open in new window

LVL 1
skaleem1Asked:
Who is Participating?
 
Tim HumphriesConnect With a Mentor DirectorCommented:
Hi, I'm not sure where we go from here.
You've said that you have no control over the spreadsheet production, so investigating the structure of the file you're being provided with will not help.
You've installed Office on the server (I presume this is working corectly) and the Interop PIAs, which appear to be registered correctly (i.e. they appear in the assembly cache). You should then be able to reference these assemblies from the script editor.The only thing I have found that could explain your inability to do this is if a setup / reinstall operation on Office has been performed that can overwrite the PIA install. The solution is to re-install the PIAs in this instance. Other than that, I can't think of a way around you problem, I'm afraid.
0
 
Tim HumphriesDirectorCommented:
You say 'manually open and save...' - is the Excel file generated / saved elsewhere or by another application?
0
 
skaleem1Author Commented:
yes, I download the excel file by using an SSIS package with http connection manager where I provide the url. This downloads the excel file on a local drive path that exists on the same server as the SQL Server. This excel file (downloaded from another server) has a total of 12 columns in it. When i use the OPENROWSET method as explained above, it only returns the first eleven (11) columns but ignores the last column. Then I manually open this excel file and save it. This time after saving, the OPENROWSET method behaves normlly and returns all 12 columns as expected. This is wiered but I need to have a solution, any clues?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Tim HumphriesDirectorCommented:
Ok, what I was really asking is how the file you download is actually created itself - sounds as though there may be someting not quite right with the structure.
Have you tried opening and saving the file through code before attempting to upload it to SQL?
0
 
skaleem1Author Commented:
Can I open and save the excel file through the vb script I am currently using in the script task? If yes, how?

Please see the script that I have in the script task in the code section:

Thanks for the help
Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    ' The execution engine calls this method when the task executes.   
    ' To access the object model, use the Dts object. Connections, variables, events,   
    ' and logging features are available as static members of the Dts class.   
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.   
    '   
    ' To open Code and Text Editor Help, press F1.   
    ' To open Object Browser, press Ctrl+Alt+J.   
    Public Sub Main()
        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"   
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
        ' Create a new HTTP client connection   
        Dim connection As New HttpClientConnection(nativeObject)

        'exteract filename for urlstring 
        Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

        Dts.Variables("User::DestTableName").Value = FullUrlString


        ' Download the file #1   
        ' Save the file from the connection manager to the local path specified   
        Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString + ".xls"
       
        'Dts.Connections("HTTP Connection Manager").ReleaseConnection(nativeObject)
        Dim SheetName As String
        Dim TableName As String

        Select Case FullUrlString
            Case "AccMaterialHierarchySource"
                SheetName = "MaterialGroup"
                TableName = FullUrlString
            Case "AccAllPartsSource"
                SheetName = "Parts"
                TableName = FullUrlString
            Case "AccPartsDocMappingSource"
                SheetName = "PartsDocMapping"
                TableName = FullUrlString
            Case "AccVendorMappingSource"
                SheetName = "VendorMapping"
                TableName = FullUrlString
            Case "AccBomSource"
                SheetName = "BOM"
                TableName = FullUrlString
            Case "AccWhereUsedSource1"
                SheetName = "WhereUsed"
                filename = Dts.Variables("User::FilePath").Value.ToString() + "\AccWhereUsedSource.xls"
                TableName = "AccWhereUsedSource"
                Dts.Variables("User::SqlString").Value = "TRUNCATE TABLE " + TableName + " INSERT INTO " + TableName + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"
            Case "AccWhereUsedSource2"
                SheetName = "WhereUsed_2"
                filename = Dts.Variables("User::FilePath").Value.ToString() + "\AccWhereUsedSource.xls"
                TableName = "AccWhereUsedSource"
                Dts.Variables("User::SqlString").Value = "INSERT INTO " + TableName + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"
            Case "AccWhereUsedSource3"
                SheetName = "WhereUsed_3"
                filename = Dts.Variables("User::FilePath").Value.ToString() + "\AccWhereUsedSource.xls"
                TableName = "AccWhereUsedSource"
                Dts.Variables("User::SqlString").Value = "INSERT INTO " + TableName + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"

        End Select

        If TableName <> "AccWhereUsedSource" Then
            Dts.Variables("User::SqlString").Value = "TRUNCATE TABLE " + TableName + " INSERT INTO " + TableName + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"
            connection.DownloadFile(filename, True)
        End If

        Dts.TaskResult = Dts.Results.Success

    End Sub


End Class

Open in new window

0
 
skaleem1Author Commented:
The issue is with the case AccVendorMappingSource,

Thanks
0
 
Tim HumphriesDirectorCommented:
You should be able to do the following:

Add:

Imports Excel = Microsoft.Office.Interop.Excel

and in your class:

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook


Then after downloading the file:

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open(filename)
        xlWorkBook.Close()
        xlApp.Quit()

0
 
Tim HumphriesDirectorCommented:
p.s. You should also set the new variables to nothing once you've finished with them.

I still think it would be good to check how the original file is created to start with. This open / save approach is a bit of a fudge.
0
 
skaleem1Author Commented:
Imports Microsoft.Office.Interop.Excel is not recognized in Microsoft Visual Studio for applications window that is opened from Script Task Design script button. When I tried to add the project reference, it is not in the list, any suggestions?
0
 
Tim HumphriesDirectorCommented:
You may just be able to use Imports Excel
0
 
skaleem1Author Commented:
The only three options available for imports in the intellisense are Microsoft, System and ScriptTask_eec62750303e4c908a25d1028465b1ab. For Microsoft, there is no Office option there
0
 
Tim HumphriesDirectorCommented:
In your script task, go to References and add a reference to Microsoft Excel object library. How this appears in  the list (under COM references) will depend on your server and the version of Excel installed. Once the reference is in place you should be able to access the Excel application functionality.
0
 
Anthony PerkinsCommented:
>>will depend on your server and the version of Excel installed.<<
You are making the assumption that MS Office is installed on the SQL Server box.  This is very rarely a good idea.
0
 
skaleem1Author Commented:
I have Windows 2003 server installed on the server however Microsoft Office is not installed and there is no option of installing Microsoft office...any other way if I can see the Microsoft Excel object library to the references list?
0
 
Anthony PerkinsCommented:
>>any other way if I can see the Microsoft Excel object library to the references list?<<
No.
0
 
skaleem1Author Commented:
I am a little lost, did not you quote above:

>>You are making the assumption that MS Office is installed on the SQL Server box.  This is very rarely a good idea.

Are you saying that Microsoft Office must be installed on the server to see the  Microsoft Excel object library in the references list?
0
 
Anthony PerkinsCommented:
>>Are you saying that Microsoft Office must be installed on the server to see the  Microsoft Excel object library in the references list?<<
Yes.  But I am also stating that it is a bad idea to install MS Office on a SQL Server box.  In other words you should find some other way to do this.
0
 
skaleem1Author Commented:
so what you think is the other way?
0
 
Anthony PerkinsCommented:
If the server is a 32-bit server than I would use OPENROWSET with the JET provider.  If it is 64-bit than you will not be able to use the JET provider and instead you can use SSIS to import the data into a staging table.
0
 
skaleem1Author Commented:
How would I know if it is a 32-bit or a 64-bit server? Is there a way to find it out? I do have access to the server...
0
 
skaleem1Author Commented:
I have determined that the computer is running a 32-bit version of Windows, so the only option left for me is to use the OPENROWSET with the JET provider as I already am, correct? So if the answer is yes, we go back to the same question of how to add Microsoft Excel object library to the references list. Do I have to install Microsoft Excel 2003 in this case?
0
 
Tim HumphriesDirectorCommented:
I think the problem comes back to  the original one of what is wrong with the structure of the Excel file that means that the last column is being ignored until it is opened / saved. Can you post the original (unaltered) spreadsheet so it's structure can be checked?
0
 
Anthony PerkinsCommented:
>>so the only option left for me is to use the OPENROWSET with the JET provider as I already am, correct? So if the answer is yes, we go back to the same question of how to add Microsoft Excel object library to the references list. <<
You do not need to install MS Office to use the JET provider.
0
 
Anthony PerkinsCommented:
>>I think the problem comes back to  the original one of what is wrong with the structure of the Excel file that means that the last column is being ignored until it is opened / saved. <<
I agree.
0
 
skaleem1Author Commented:
Because of the business sensitive data, unfortunately I can not send the unaltered spreadsheet however I would appreciate if you can walk me through how I can check the structure of the file.

acprkins,
your quote:
>>You do not need to install MS Office to use the JET provider
what do i need to do to have the Microsoft Excel object library added to the references list?
0
 
Anthony PerkinsCommented:
>>Because of the business sensitive data, unfortunately I can not send the unaltered spreadsheet<<
No one is asking you to upload your sensitive business data.  All you have to do is create an exact copy and obfuscate (make unrecognizable) the data.  Then see if you can duplicate the problem.  If you can then upload that copy of the data.

They are two separate animals:
1. The JET provider should already be installed, so there is nothing to be done.  
2. The Microsoft Excel object library is installed when you install MS Office.  For many reasons I do NOT recommend you do this.
0
 
skaleem1Author Commented:
If you read in the trail above, the file is created by a process I do not have access to. If I obfuscate the data manually, I have to open and save it, this is when it starts working correctly without any issues (explained above).

>>The Microsoft Excel object library is installed when you install MS Office.  For many reasons I do NOT recommend you do this.

So then what else should I do to have the Microsoft Excel object library added to the references list?
0
 
Anthony PerkinsCommented:
>>So then what else should I do to have the Microsoft Excel object library added to the references list?<<
You do not need it.  All you need is get OPENROWSET to work correctly.
0
 
skaleem1Author Commented:
It is working correctly except that it is ignoring the last column, wiered behaviour. However, if i manually open and save it, it even correctly includes the last column. That is why i was thinking to use the Excel library to open and save the excel file in code to automate the process, what is the solution then?
0
 
Anthony PerkinsCommented:
>>That is why i was thinking to use the Excel library to open and save the excel file in code to automate the process, what is the solution then?<<
Then you will have to install MS Office.
0
 
skaleem1Author Commented:
Can't I get away without the installation of the MS server as this will cause them to install it on the production server in future which I am not sure if it would be possible. Is it possible to just copy the related dll to a specific path on that server to achieve my goals
0
 
Tim HumphriesDirectorCommented:
I'm not sure about this on two counts:

1. I doubt that the functionality required is supported by a single dll.
2. you may fall foul of licensing issues.

Can you not find out more about how the source spreadsheet is created? Is it created by a specific user for example? If so what version of Excel  are they using, what OS etc. Better to resolve the problem at source rather than the open / save fudge.
0
 
skaleem1Author Commented:
I understand and I have already asked the folks who are responsible for creating the Excel file

Secondly, in the mean time until I get the answer, as suggested by acperkins, I have installed MS Office 2003 however I do not see the Excel library added to the references list. Is there any additional interim step I have to execute to make it appear in the references list?
0
 
Anthony PerkinsCommented:
I am afraid I have no idea.
0
 
skaleem1Author Commented:
ok admitting you have no ideas is a good sign :-)
0
 
Tim HumphriesDirectorCommented:
From SSIS 2005 script task, you can only reference .Net assemblies. As the interop library is COM you will need to download the 'Primary Interop Assemblies' and you can then reference that. This is effectively a .Net bridge to the underlying COM object.

You can download the PIAs for office 2003 from here :

http://www.microsoft.com/downloads/details.aspx?FamilyID=3C9A983A-AC14-4125-8BA0-D36D67E0F4AD&displaylang=en

Sorry - I haven't had this exact problem and had missed that you couldn't reference the COM objects directly from SSIS script task (in 2005 - in 2008 you can).
0
 
skaleem1Author Commented:
TimHumphries,

Thanks for the suggestion. I have successfully downloaded the PIAs for office 2003 however it does not allow me to see the Microsoft Office as a project reference to be able to utilize it. Any clues?
0
 
Tim HumphriesDirectorCommented:
Ok, so you've downloaded the package, and installed the MSI and when you try to add a reference there is no entry for Microsoft.Office.Interop.Excel ?

Has the install added the DLL references to the glocal assembly cache?
0
 
skaleem1Author Commented:
Where and how can I check if the install has added the DLL references to the glocal assembly cache?
0
 
Tim HumphriesDirectorCommented:
Look in the directory Windows\Assembly - should list all installed DLLs
0
 
skaleem1Author Commented:
Seems like it does, see the attached screenshot. Do I have to reboot the server to have the Microsoft Visual studio for Application pick that, just curious?
WindowsAssemblyExcelDLL.GIF
0
 
Tim HumphriesDirectorCommented:
No, should be accessible straight away. Odd. With the dlls in the GAC I would expect you to be able to see them when you select 'add reference' from your script task.
I guess a reboot wouldn't hurt - or closing / opening the script task in case it hasn't refreshed it's list of available references. Don't really understand why you can't see them, I'm afraid.
0
 
skaleem1Author Commented:
I rebooted the computer but to no avail. Same old story. What is the exact name of the reference that should appear in the .Net Add Reference list
0
 
Tim HumphriesDirectorCommented:
I think they are all listed in the file o2003PIA_ReadMe.rft, which is part of the package download.

You'll want Microsoft.Office.Interop.Excel
0
 
skaleem1Author Commented:
is there any other hint you can think of?
0
 
Tim HumphriesDirectorCommented:
Do you have access to any other development environment on the machine (e.g. Visual Studio 2003 with VB.Net maybe) from which you could check whether the references are visible?
Afraid I'm running out of ideas on this one...
0
 
skaleem1Author Commented:
Well this is only a server running SQL Server, SSIS and Reporting services. The only exception I was allowed to make was to install Microsoft Office as suggested by acperkins. However that did not resolve the issue. Then I installed the PIA as suggested by you. Even uninstalled and reinstalled the PIA but no luck. I even rebooted the server and still no change. Is there any missing piece to connect?
0
 
Tim HumphriesDirectorCommented:
Could you try this: create a new report in SSRS and go to report / properties and see if you can add the references in that environment. Not any direct help, I know, but may help shed more light on the problem.
0
 
skaleem1Author Commented:
I tried as you suggested however failed to add References through Report/Property. The interface allows me to add reference utilizing:

(1) .NET tab - could not find the Mocrosoft.Office.Interop.Excel in the references list like SSIS
(2) Browse tab - I could browse and go to the Windows/Assembly folder and select the Mocrosoft.Office.Interop.Excel assembly, however I can not add it to the references as the interface only allows the dlls to be uploaded as reference while this assembly is not a dll (Can I somehow get the dll and reference this is a question to answer)
(3) Recent tab - All buttons (Add, Remove are disabled) - so no luck

Does it show any light at the end of the tunnel?
0
 
Tim HumphriesDirectorCommented:
The entries in the GAC are just references. If you are to browse for the DLL you need to find the physical location of the DLL (where you installed the PIA) and try to use that.
0
 
skaleem1Author Commented:
For the Browse tab - I could browse and go to the Windows/Assembly folder and select the Mocrosoft.Office.Interop.Excel assembly, however I can not add it to the references as the interface only allows the dlls to be uploaded as reference while this assembly is not a dll (Can I somehow get the dll and reference this is a question to answer). How and where can I find the physical location of the dll?
0
 
Tim HumphriesDirectorCommented:
I guess searching for it is the safest this to do - I'm not sure where it will have been physically installed.
0
 
skaleem1Author Commented:
well nothing work, I am not sure what should I do with this question, your suggestion?
0
 
Anthony PerkinsCommented:
>> I am not sure what should I do with this question, your suggestion?<<
You should award points to TimHumphries for making every attempt to assist you.
0
 
skaleem1Author Commented:
Thanks a lot for all the efforts you made. I have asked the folks who are responsible for generating the file to add a dummy column to the excel file and this has resolved the issue as a workaround. The last dummy column is ignored as expected (the key issue) and the second last column (that used to be the last column before adding the dummy column) comes out correctly with all the data.
0
 
Tim HumphriesDirectorCommented:
Skaleem: Glad you've been able to get a solution, although the import problem remains a mystery, I guess.

acperkins: thanks for your input and closing comment :-)

Tim
0
All Courses

From novice to tech pro — start learning today.