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 DTS Package from one server to another

Hi all,
I have a need to copy a DTS Package from one server to another, is this possible?
Thanks,
KenI found the answer I was looking for! Guess Coffee is in need this morning!

Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
===================================
No description found (Copy Database Wizard)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard. PackageCreator.SavePackage()
A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error – see below) and
the maintenance plan designer will not save a plan (it gets a "no description
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL server
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E – all without error.
It is likely that the SQL Server 2005 installation routine is fragile – and
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/Sho...29472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.Pack ageLocationControl.SavePackage(Package package)
at
Microsoft.DataTransformationServices.Design.Contro ls.PackageSaveCopyForm.PackageSaveCopyForm_FormClo sing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:

> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creating
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================
> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard. PackageCreator.SavePackage()
>
>
|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
something…
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC
|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC
|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database “programming” model and want to pretend its not there…
MikeC
|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC
sqlsql

Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
===================================
No description found (Copy Database Wizard)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage
()A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error – see below) and
the maintenance plan designer will not save a plan (it gets a "no descriptio
n
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C
:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL serve
r
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E – all without error.
It is likely that the SQL Server 2005 installation routine is fragile – an
d
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/Sh...129472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePac
kage(Package package)
at
Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.Pac
kageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:

> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creatin
g
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I hav
e
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================
> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Packag
e
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePacka
ge()
>
>|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
something…
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database “programming” model and want to pretend its not there…
MikeC|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC

Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
=================================== No description found (Copy Database Wizard)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error â' see below) and
the maintenance plan designer will not save a plan (it gets a "no description
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL server
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E:) â' all without error.
It is likely that the SQL Server 2005 installation routine is fragile â' and
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=129472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at
Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:
> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creating
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()
>
>|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
somethingâ?¦
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database â'programmingâ' model and want to pretend its not thereâ?¦
MikeC|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database Wizard Error- SQL Server Agent cannot execute the SSIS package

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->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)

Where did you run your package?|||The Copy Database Wizard ran the SSIS package automatically, at the end of the wizard.|||The Copy Database Wizard ran the SSIS package automatically.|||Oops. I thought you were using transfer database task in SSIS. I think you are at the wrong forum. I assume you initiated copy database wizard from sql management studio.Try posting your issue at the right forum:
SQL Management studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597172&SiteID=1
SMO: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

Also, before doing that you may want to check if both the source and destination servers support remote connections.|||Thanks for pointing me to the fourm. Yes I am using Management Studio for the Copy Database Wizard. Could you please tell me how I can check to see if the source and the destination servers allow remote connections? Is that a Windows Server2003 setting or a SQL Server2005 setting?
Thanks!|||1. Open "SQL Server 2005 Surface Area Configuration" (in Programs-> Microsoft SQL Server 2005 -> Configuration Tools)
2. Click on "Surface Area Configuraiton for Services and Connections"
3. Select "Remote Connections" option for the instance you are interested in.
4. Select "Local and Remove connections"

In SQL Server Management studio, right click on the instance, select properties, go to connections page and select "Allow remote connections tot his server".

I do not know what the default settings are. So, check both.|||When I click on Remote Connections, I get a message saying"You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services"

Do you know how/where I can do that?
Thanks again!

Copy Database Wizard

I want to copy a database that is currently in use. The
DTS package fails if any user is connected. What command
would I issue to block all new connections to the
database? With new connections blocked and existing ones
finished, I should be able to copy the file the way the
wizard configures the package.Check out the ALTER DATABASE command in BOL
Nathan H.O.|||I assume that you are recommending something like:
ALTER DATABASE myDB SET SINGLE_USER
This command locks when the database is already in use.
>--Original Message--
>Check out the ALTER DATABASE command in BOL
>Nathan H.O.
>|||Not if you specify the proper ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Dan" <deletethisprefixandthefirstperiod.cathedr@.dshs.wa.gov> wrote in message
news:2aab201c39270$35205650$a601280a@.phx.gbl...
> I assume that you are recommending something like:
> ALTER DATABASE myDB SET SINGLE_USER
> This command locks when the database is already in use.
>
> >--Original Message--
> >Check out the ALTER DATABASE command in BOL
> >
> >Nathan H.O.
> >

Copy Database Wizard

When I choose to have the database copy occur on a daily
basis, I can't find the job or package (whichever it is
creating) anywhere. Does anyone know how this gets
saved/scheduled or where I can view the properties/add
steps?The package that gets created should be on the destination server.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Copy Database from One SQL server to another fails

I am try to run a DTS package to copy a database from one SQL server to
another one.
The DTS package was running fine till yesterday morning but now I am getting
the error message
The Bulk Copy Execution Failed
Error Code 80045707
any thoughts?
drew
Does your SQL Server Service user have enough rights to copy files over the
network?
It might be a problem..
"drew" wrote:

> I am try to run a DTS package to copy a database from one SQL server to
> another one.
> The DTS package was running fine till yesterday morning but now I am getting
> the error message
> The Bulk Copy Execution Failed
> Error Code 80045707
> any thoughts?
> --
> drew
|||Yes the user right are okay, using SA on SQL and Administrator on the windows
2003 servers.
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Does your SQL Server Service user have enough rights to copy files over the
> network?
> It might be a problem..
>
> "drew" wrote:
|||Hi,
Sorry if i'm not clear, what i meant is SQL Server Service on both SQL
servers are not running with Local System Account right?
"drew" wrote:
[vbcol=seagreen]
> Yes the user right are okay, using SA on SQL and Administrator on the windows
> 2003 servers.
> "Umut Nazlica" wrote:
|||The user account are okay. It seems to be a problem the database it self. If
we try and backup and the restore the database to another name on the same
SQL instance. The database hangs on the restore saying it is loading.
Regards Drew
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Hi,
> Sorry if i'm not clear, what i meant is SQL Server Service on both SQL
> servers are not running with Local System Account right?
>
> "drew" wrote:
|||> The user account are okay. It seems to be a problem the database it self.
That would surprise me.

> If
> we try and backup and the restore the database to another name on the same
> SQL instance. The database hangs on the restore saying it is loading.
That is expected if you do the restore using NORECOVERY. Try using RECOVERY instead. Also, make sure
it isn't just EM that doesn't refreshed. Try logging in using Query Analyzer and access the
database.
My guess is that there has been some account changes for the Agent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"drew" <drew@.discussions.microsoft.com> wrote in message
news:B128608B-0E23-4D66-BE8B-B40CC941B09C@.microsoft.com...[vbcol=seagreen]
> The user account are okay. It seems to be a problem the database it self. If
> we try and backup and the restore the database to another name on the same
> SQL instance. The database hangs on the restore saying it is loading.
> Regards Drew
> "Umut Nazlica" wrote:
|||We got the backup and restore working okay but still problems with the DTS
package.
We tried copying the database to another name name on the same SQL instance
but still get the problem.
If I detach the database and copy it to aother SQL server then attach it
there I again get the problem of not being able to copy it to another name on
the same SQL instance.
If I copy just the objects not the data everything is okay. If I then
compare the databases with SQL compare everything is okay.
If I try to use the DTS copy databse task to just copy the data. Using SQL
compare the permmisions on the databse tables change with the public role
being dropped and the not null constraint check becomig just not null.
Talkig with Microsoft but still not much further forward.
"Tibor Karaszi" wrote:

> That would surprise me.
>
> That is expected if you do the restore using NORECOVERY. Try using RECOVERY instead. Also, make sure
> it isn't just EM that doesn't refreshed. Try logging in using Query Analyzer and access the
> database.
> My guess is that there has been some account changes for the Agent.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "drew" <drew@.discussions.microsoft.com> wrote in message
> news:B128608B-0E23-4D66-BE8B-B40CC941B09C@.microsoft.com...
>
>
|||we final got the problem resolved with the help of Kevin at MSDN.
In the database some had created a blob bigger than 8 Meg, this caused the
DTS package to fail. hen we romved the offending records the package ran
okay.
see KB 257425
regards Drew
"drew" wrote:
[vbcol=seagreen]
> We got the backup and restore working okay but still problems with the DTS
> package.
> We tried copying the database to another name name on the same SQL instance
> but still get the problem.
> If I detach the database and copy it to aother SQL server then attach it
> there I again get the problem of not being able to copy it to another name on
> the same SQL instance.
> If I copy just the objects not the data everything is okay. If I then
> compare the databases with SQL compare everything is okay.
> If I try to use the DTS copy databse task to just copy the data. Using SQL
> compare the permmisions on the databse tables change with the public role
> being dropped and the not null constraint check becomig just not null.
> Talkig with Microsoft but still not much further forward.
> "Tibor Karaszi" wrote:

Copy database fails because of invalid object name in a view?

I'm getting the below error when attempting to copy a database. The view works just fine but the package keeps giving me the below error:

07 3:26:37 PM,4/11/2007 3:26:37 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "
CREATE VIEW [app].[vwbaseTransfer]
AS
Select a.app_id, a.app_year, u.first_name, u.middle_name, u.last_name, app_phoneAM, email, CONVERT(varchar(10), app_dob, 101) as app_dob, app_citizen,
CASE WHEN app_untilDate > GetDate() Then app_addr1 ELSE app_pAddr1 END AS app_addr1,
CASE WHEN app_untilDate > GetDate() Then app_addr2 ELSE app_pAddr2 END AS app_addr2,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr3,
CASE WHEN app_untilDate > GetDate() Then app_addr3 ELSE app_pAddr3 END AS app_addr4,
CASE WHEN app_untilDate > GetDate() Then app_city ELSE app_pcity END AS app_city,
CASE WHEN app_untilDate > GetDate() Then app_state ELSE app_pstate END AS app_state,
CASE WHEN app_untilDate > GetDate() Then app_zipcode ELSE app_pzipcode END AS app_zipcode,
app_sex, app_racecode, app_accept_letter, app_acceptDate, app_orientation_date, app_ssn
FROM applicant a inner join uop_user u on u.id = a.app_id
where app_accept_letter is not null and app_acceptDate is not null

" failed with the following error: "Invalid object name 'applicant'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

Just out of curiosity, is applicant in a different schema than "app"?|||Same schema|||Maybe it is trying to copy the view before the applicant table?|||

jwelch wrote:

Maybe it is trying to copy the view before the applicant table?

Ooooo, I like that thought!|||That's not good. Anyone tried this before?|||Should I assume that this will not work and manually copy databases?|||

Bogey1 wrote:

Should I assume that this will not work and manually copy databases?

What task are you using?

Also, isn't there a better method of "copying" databases? (That is, a backup/restore process.) Are you going for just the schema, or data as well?|||

Bogey1 wrote:

That's not good. Anyone tried this before?

If you feel like wading through it, you could check your sys.sql_dependencies view to see if it has the dependency between the view and the table. Not sure if that is used to determine the order when db objects are copied, but it might be worth a shot.

|||

I'm using the copy database task. If I do a backup and restore then I manually must reset the logins, correct. I was hoping that a simple copy database would take care of this.

thanks.

copy database

when i'm copying my database from one server to another server the package is faling with the follwoing errors

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'mmm\servicestart'.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700

What is the exact problem with these errors and what should i do to overcome this.Can anyone help me in this regard.

Thanks.Make sure the login account is a domain account with permission to access the target server.|||which acount u r talking abt?service start or my login account...?|||the account for sqlserveragent. what is mmm\servicestart?|||yes, the sqlserver agent user in source server is mmm\administrator and in the destination server is mmm\servicestart.
The logins are not same for the sqlserver agents on the two servers.
Is that the reason behind the error?
So how should i overcome this problem?

Thanks for ur time and efforts in this issue.|||Originally posted by bruce_Reid
yes, the sqlserver agent user in source server is mmm\administrator and in the destination server is mmm\servicestart.
The logins are not same for the sqlserver agents on the two servers.
Is that the reason behind the error?
So how should i overcome this problem?

Thanks for ur time and efforts in this issue.

Set the same accounts for your servers and agents.|||should I just create the user or i should change the login in the sqlserver agent to the source server(mmm\admin...) to the user of the destination(mmm\servicestart). ANd should the passwords the same for the logins on the two servers means for the login servicestart on both source and target servers.
Can u please let me know the detailed procedure to do this copy...
Thanks.|||Originally posted by bruce_Reid
should I just create the user or i should change the login in the sqlserver agent to the source server(mmm\admin...) to the user of the destination(mmm\servicestart). ANd should the passwords the same for the logins on the two servers means for the login servicestart on both source and target servers.
Can u please let me know the detailed procedure to do this copy...
Thanks.

You should have domain account with sufficient rights for designed activity (DTS, network, etc.). Use this account as Log On instead of Local System account (see Control Panel\Administrative Tools/Services/MSSQLSERVER/Properties and the same for MSSQL Agent) for both your servers. Also you have to add login for domain\account on servers and add this account to System Administrators server role. After that your servers will have the same rights in domain.

Copy Data to excel file using dts package in sql server 2000

Friends

Any one of you share your knowledge how to transfer data from a database to a excel using dts packages in sqlserver 2000.

I want clear steps how to create a dts package

Appreciate your help

Thanks

satish

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

You might try this group instead, as this forum is for SQL Server Integration Services

Copy data between DB with DTS

Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.Lookup Data Driven Query task in SQL BOL. It will allow you to check for the existence of a record on the target server and skip that record when processing. It's a little complex to use, but it works well.

One more caution, since it is a script-driven task, you may experience performance issues with very large record sets.

You may also consider using straight T-SQL and not use DTS at all. Even if the two databases are are separate servers, you can link them and use T-SQL.

Regards,

Hugh Scott

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||You can use query statement to copy the data into db2.

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.

Hi,

i can recommend a tool for this problem:
http://www.sqlscripter.com

Regards
Thomas

Copy annotations out of xml/ dtsx package

Is there a way to strip out the annotations from a dtsx package. I'm looking at quite a few and would like to automaticly pull those out and store them somewhere in either a table or a text file.

Is this possible or am I going to have to hand copy them out?

Thanks for the help
Saitham8

The dtsx file is in xml format, so one would think that this would be possible with an XPath query. However, I made a copy of my dtsx file, then added several annotations and saved. The modified dtsx file showed a new last modified date, but when I used WinMerge and fc (even fc /b - for a binary comparison) there were no changes.

So then I created a brand new blank SSIS package. Then I saved it and made a copy of the entire solution directory structure. Then I added an annotation and saved and closed BIDS. Then I used WinMerge to compare the two directory structures. The only file that was different was the Integration Services Project3.suo file which is at the solution directory level.

To further test I opened up the unaltered copy deleted its blank package and then imported the annotated package. It came up without annotations!

Conclusion: the annotations are stored in the solution in the .suo file!

What kind of design flaw is this!

|||

If that were true then I wouldn't be able to copy a package containing an annotation into a seperate package and have the annotation show up. Which I can.

-Jamie

|||

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

|||

David Lundell wrote:

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

AHA. I am on SP1. Perhaps that's why.

-Jamie

|||So it sounds like I'll need to move up to sp1 and then take a look for the anotation tag in the xml. I won't have a chance to get to this for a little while but I'll let you know what I find.|||

I upgraded to SP 1 and saw the same behavior as before.

I create a blank IS project

I copy the project to a new directory.

I annotate the otherwise blank package.dtsx file

I hit the save all button.

I then use winmerge to compare the two directory structures. Again the only file I see that has changed is .suo

So I open the untouched copy. I delete its package.dtsx. I then import existing package and grab it from the original project. I don't see the annotation.

Jamie, could you provide us a step by step of how you get the annotations to move with the package?

|||Everytime you save a modified package in designer, at least two properties are changed: VersionGuid (new GUID is generated) and VersionBuild (build is incremented), so the dtsx files should differ.
I would check if you've saved the modified package.
Once you added an annotation, click away from the annotation on some other place in control flow. The title of the document should get asterisk at the end (like "Package 1.dtsx [Design]*", meaning it is modified and will be saved. Now click save all.
Once the modified file has been saved, windiff should show quite a bit of differences in DTSX files. The annotations and diagram layouts are stored in DTSX file, not in SUO files. This is true both for RTM and SP1 builds.|||

User error. The annotations are stored in the package, not in the SUO file.

You can't use XSLT to strip those out unless you can convert the binary encodings which is how they're persisted.

K

|||

Michael,

I have tried what you suggested. I added the annotation and then clicked in some other place in the control flow. The title of the document did not get an asterisk. I did click Save, and I also tried Save All. I still got the same result.

Now to report the real bug! I closed the package and then when I reopened it the annotation was gone. Since all of these were throwaway packages I was creating just to research the issue for the original posting I did not have a need to go back into them. I did so this morning, and the annotation did not stick in any of them.

So I modified my experiment. I added something else to the control flow, an ActiveX Script task. Then I saved it and copied the whole solution directory. Then I added the annotation (no asterisk appeared). Then I renamed the ActiveX Script Task. At that point I got the asterisk to appear. Then in the comparison I see a difference in the package.dtsx. It looks to me like the text of the annotation is stored in the binary attribute of the ddsxmlobjectstreaminitwrapper element. Then when I move the package the annotation comes with it.

So the bug is if the only change I make is adding or modifying an annotation then BIDS does not indicate that I have changed the package and hence does not save the new or modified annotation! Please try it and you will find the bug. In summary if I go into a package for the sole purpose of adding and/or modifying annotations they will not get saved.

I am glad however to discover that I was wrong about the .suo file, because that sure did not make sense! But based on the actual bug reported and my exact steps you can see how it looked like that!

Thanks for the help, and please report back on what you find out with this bug.

|||David I was noticing that the annotations disapear if you don't have any text in them. I still havent gotten a chance to try my hand at this stuff. Thanks a ton for looking into it.|||

Kirk,

You are correct. The annotations are stored in the package.

However, as I documented in my reply to Michael Entin - MSFT ,
there is a bug in BIDS that caused the behavior I described.

Essentially, if all you do is add or modify annotations those changes do not get saved at all. That is why I only saw changes in the .suo

Copy and Paste Minor Irritation

Can anyone explain why when I copy a data element and attempt to paste it it always appears waaay off screen in my SSIS 2005 package? Why won't it paste where I am right-clicking and telling it to go? I hate having to hunt for the element in the flow.

To save you hunting, the components always appear at the lower left corner or the upper right corner (as far as I've seen).

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copied Package ConnectionManager has photographic memory?

I originally made 1 package, got it the way I wanted with configurations,etc, and then made 30 copies of it, renaming, etc.

The original package (and the copies) has 2 connections. Connection A is set at runtime by a configuration from an XML file. Connection B is set from a SQL Server lookup from Connection A.

I'm now trying to run one of those copied packages. As I mentioned, in my package I have 2 connections. I changed one of my connections, save the package, go back and look at the connection, and the initial catalog has changed. Life is good, right? Wrong. I close the package, go back in, and the initial catalog is back to the original. If I look at the package's XML in notepad, I see the correct new connection manager. So where is it picking up this info from?

Interestingly, if I delete the connection, add a new connection, rename it to the original connection I had (so my configuration will still be good), save it, close it, go back in, it still reverts back to that original connection.

Whats the deal? Its almost like I have to name my connections differently across packages?Check if it is configured in the configuration settings
(in this case it is loaded from config provider) or
maybe in the property expressions (in which case it
is calculated).|||wow. I must be burnt out. I see the problem. User error. thanks