Copy Excel sheet to SQL 2005(Dev Edit X64) via SSIS

Ok...

This worked on the laptop I had last week. That laptop has ceased to be. I have a new one, which may be my problem.

I have a package that takes an Excel Sheet, makes a couple of conversions via the Data Conversion object, and places the rows into my SQL 2005 X64 database.

Here are the major exceptions:

1. [Excel Source [2479]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (THERE WERE NOT)

2. [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Here is the rub... I am running Vista(Weeeee), and Office 2007(Double Weeeeee). Last week I was running XP, and Office 2003.

Is this a wierd Vista permission problem? The "Class not Registered" message is worrying me. Does SSIS work with Excel 2007 installed?

Xig

Xig,

Here is a link to considerations for SSIS on 64-bit computers: http://msdn2.microsoft.com/en-us/library/ms141766.aspx.

One of the things to note is that there is no 64-bit Jet driver, so connections to Access and Excel don't work in 64-bit mode. You can however run your packages in 32-bit mode. The link provided above has additional details.

Thanks,
Patrik

Copy Database Wizard

I'm trying to copy a database from my laptop to my web server over the Internet using a VPN.
After solving all the common well documented problems with getting this to work, it now fails on the last step, where its supposed to be executing the job. The event log on the server says it failed to connect to my laptop. ("Failed to connect to server xxx") The event log on the laptop says that the server failed to log on. If I use the sa account it says the login failed for user 'sa', and if I use Windows Authentication it says SSPI handshake failed with error code 0x8009030c ... connection has been closed.
Of course this makes no sense as the first 2 steps of the wizard test that the login can be performed, and the SQL Management studio on the server can connect to my laptop fine either way.
I just spent several hours getting the encrypted connection to work between my laptop and the server. The server is in texas, i'm in BC. I had to set up a VPN, edit the windows hosts file to map the computer names to the VPN ip's, export a certificate from the server to my laptop, and change the sql server to run as 'local service' instead of 'network service' just to get this far.
Isn't there an easier way to do this? Why can't I just create a database with the same name on the server and overwrite the database files with ones from my laptop. It would take about 30 seconds instead of 30 hours...
Could any of the other features of sql server help me with this? replication? subscriptions? linked servers? data export wizards?
i noticed in the event log on the source computer if i use windows authenticaiton it fails to connect to the server as 'anonymous user' or something like that. maybe thats a clue?|||bump...
i solved my problem by copying a backup file to the server and got the restore working. But i still want to know why this doesn't work. I have problems with other wizards too, and knowing whats going on here might help me fix the other problems.

Copy Database Problem SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the SERVER to any place in my laptop in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Check your duplicate post here.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Okay. I just didn't know which one is the right place to post this question.

Thanks anyway.

Copy Database Error using SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the net server called "SERVER" to any place in my laptop called "MOSHE-DELL" in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Hi,

There are a few things to check to help you along abit.

1) Connect to your SQL server via the "SQL Server Configuration Manager" and check services and that "Client Protocols" have TCP/IP and named pipes configured.
2)You should also ensure yiu are connecting via the correct machie and instance name be default it will be MOSHE-DELL\MSSQLSERVER.

CU

tosc

|||

Hi Tosc,

Where should I configure my SQL SERVER 2005 (in the local machine) in order to allow remote connections?

|||check the firewall on the Moshe-Dell server and verify that the correct ports are open.|||I have tried following the indications in: http://www.west-wind.com/WebLog/posts/4493.aspx and: http://support.microsoft.com/kb/914277 but this time getting the Error below: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent is not currently running so it cannot be notified of this action. (.Net SqlClient Data Provider)
|||

Hi,

By default, the SQL Server Agent service is disabled when SQL Server 2005 is installed unless the user explicitly chooses to autostart the service

By using the SQL Server 2005 Surface Area Configuration you could configure the SQLSERVERAGENT service!

Configuring SQL Server Agent

cu

tosc

|||

Using SSMS, on the left pane (Object Explorer), right click on SQL Server Agent. Select Start.

Then again right click on SQL Server Agent, select Properties. Verify that the AutoStart boxes are checked for both SQL Server and SQL Server Agent.