Import from excel

I am trying to import data from an excel spreadsheet. The file comes from a client and sometimes it may not have any data, that is, it will be blank. It seems that the Excel Source in SSIS does not like empty files, is there any way around this? I suppose I can check to see if it is empty and abort the import process but I am not sure how to do this. Any other ideas on how to get around this?

mthierauf wrote:

Any other ideas on how to get around this?

you could have the package fail if the excel file is empty. in other words, you would first check the contents of the excel file, then determine how the package should proceed based whether the contents is empty or not. you could use the script task to check the contents of the excel file.|||

Thanks Duane... I was trying to do that but wasn't sure how to check for an empty excel file. I assume you would do this in a script but I am not sure what function does this. I may not even need to do this now anyway, our client is going to put a header row in the file... but I would still appreciate the code to check for an empty excel file.

|||

mthierauf wrote:

Thanks Duane... I was trying to do that but wasn't sure how to check for an empty excel file. I assume you would do this in a script but I am not sure what function does this. I may not even need to do this now anyway, our client is going to put a header row in the file... but I would still appreciate the code to check for an empty excel file.

did you ask this question on the visual studio tools for office forum?

Import Data on 2005, Many Errors

I have a client who we moved to our new web server and sql 2005 server from a sql 2000 server. I detached the database from sql 2000 and attached it to 2005. I also just set the compatibility to 2005 also.

My client used enterprise manager to import data into the tables on the sql 2000 just fine. Now using the SQL Management Studio, importing the same table produces all kinds of error, truncation errors, etc.

I have played with a bunch of the settings, did the "Suggest Types" options, but I still just get a bunch of errors. It seem to get it to work I have to go in on the columns of the flat file i am importing and change EVERY COLUMN field to match the table i'm importing too. That is just too much work.

I basically have a 2 record text file i easily imported to sql 2000. but importing into sql 2005 proves to be a *** load of work! Aren't products supposed to get better with future releases? What am I doing wrong?

I've tried the sql native client and the oledb sqlserver client and get the same results.

Any ideas?

Hi, so your Launching the Import/Export Wizard from Mgt studio?

Is it a delimted file, fixed width, variable row length...

What are some of the errors you are seeing.

Import data from Adaptive Server Anywhere 7 DB

My client has a custom app using a Sybase Adaptive Server Anywhere 7 DB
backend. I need to regularly extract read-only data from a couple of
tables in this database and I would have thought it would be simple to
setup a System DSN for this purpose, however I am finding it difficult
to locate an appropriate ODBC driver, without having to pay Sybase a
large fee for the privilege. I have never used any Sybase before, what
should I do?

Cheers,

StephenYou can Google for free Sybase clients/ODBC drivers, if that's what you
need, although I don't know whether or not any of them would be
suitable for you. Alternatively, look at it the other way round and ask
the client to export the data to flat files, so you can import it into
MSSQL.

Simon

Import a COM+ application That depends on SQLNCLI on 32 bit XP Fails.

We have a 32 bit COM+ application that depends on SQLNCLI.DLL for BCP functionality. We are upgrading to the SQL Server Native client and moving away from ODBCBCP.dll.

Anyone running 32 bit XP attempting to import the COM+ application gets the following message: The DLL could not be loaded. Check to make sure all required application runtime files and other dependent DLLs are available in the component DLL's directory or the system path.

If I revert the DLL back to using ODBCBCP.dll the application will import and run fine. Here is the interesting part. If I import the application with the ODBCBCP.dll then rebuild the DLL so it links against SQLNCLI.DLL the app will run fine. It has no problem finding and loading SQLNCLI.DLL along with all of it's dependencies.

Anyone running 64 bit XP can import the COM+ application when the DLL is linked against SQLNCLI.DLL.

Does anyone have any insight into this issue. It is causing our developers alot of headache. If sample code is needed I will be glad to provide it, but only at request.

Thanks for your time,
Mike

To use the bcp APIs you have to link with a .lib that has the bcp API entry points because they are not accessed through the driver manager like mainstream ODBC APIs. With the MDAC 'SQL Server' driver you have to link with odbcbcp.lib and the bcp APIs are in a seperate odbcbcp.dll. With 'SQL Native Client' you have to link with sqlncli.lib - for SQL Native Client we put everything into a single dll - APIs, bcp and netlibs. If an app is linked with odbcbcp.lib but loads sqlsrv32.dll (or links with sqlncli.lib and loads sqlsrv32.dll) then you get an error when you load the app.

This is covered in Books Online in the topic which deals with upgrading applicatsion from MDAC to SQL Native Client(http://msdn2.microsoft.com/en-us/library/ms131035.aspx), which says:

"SQL Native client is not compatible with odbcbcp.dll. Applications which use both ODBC and bcp APIs must be rebuilt to link with sqlncli.lib in order to use SQL Native Client."

Applications which use only the 'standard' ODBC calls only link with the driver manager (odbc32.dll) so don't have a dependency on the driver dll (or bcp dll).

|||I was aware of that and made sure that our entire app only linked with sqlncli.lib. I changed that DLL to be delay loaded and our problem went away and the app runs fine.