none
Execution of Package failed

    Question

  • I am trying to design package which would copy all tables and stored procedures from one server to another. It used to be simple task in SQL 2000, there was wizard. However the new wizard does not work...

    I wrote SSIS package, but when I execute I get the following error: "Can not apply value null to property Login..."

    Any idea? It seems to me that this new SQl2005 is not that simple to use like 2000. 2000 was a great product. I am considering to rollback to 2000. Very frustrated with the new one... 

     

     

     

     

    Thursday, January 12, 2006 7:17 PM

All replies

  • Anybody can help?
    Friday, January 13, 2006 5:50 AM
  • There should be more to the error message than that. Please can you copy the full details? It would help to know what task is failing.

    -Jamie

     

    Friday, January 13, 2006 6:54 AM
  • Thanks. Here it is:

    Progress: Transfering objects. - 0 percent complete

    [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

    Is there any template I can use to transfer data from my server to the remote hosting server? I have tried everything... I am new to this SSIS, I just need a simple way to transfer all data, so my server is updated with the laptop's development database. I do not really have time to study tons of pages. There use to be good DTS wizard in the previous version of SQL Server... It could drop the tables and do it all. This one I can't get to work, so trying to write this package...

    Friday, January 13, 2006 7:15 AM
  • Please help. Is there any sample SSIS package I can use to transfer all objects from one database to another? Do I need to have rights to create database in order to use this (Transfer SQl Server objects) control?
    Saturday, January 14, 2006 10:00 PM
  • This seems like a security issue. Check the connection string of your connection manager.

    And remember - by default, passwords will not get stored in the package. If they do, they will be encrypted. Check the package's protection level.

    -Jamie

     

    Saturday, January 14, 2006 11:27 PM
  • This is the connection string to the remote server:

    SqlServerName=sql2k501.discountasp.net;UseWindowsAuthentication=False;UserName=SQL2005_194896_jiptravelco_user;

    It appears the password is set, since the password proprty shows; *****

    This is the local server connection string:
    SqlServerName=h001335;UseWindowsAuthentication=True;UserName=Administrator;

    Connection Manager type: SMO Server for both

    How do I check package protection level?


    Sunday, January 15, 2006 12:05 AM
  • Do I need to be Db Owner in order to use SQL Transfer Objects control?
    Sunday, January 15, 2006 12:08 AM
  • No, but you need to have access to the objects that you are trying to transfer.

    As you can see above, your connection string for the remote server does not contain a password. You say that somewhere you can see *****. Where can you see that?

    ProtectionLevel is a property of the package.

    -Jamie

     

    Sunday, January 15, 2006 9:14 AM
  • The password *** I saw in the properties of the Connection, same like Connection String, just another property.

    Is there any update to SQL 2005 I may have missing? How to get the latest SQL Server 2005 update?

    Monday, January 16, 2006 12:26 AM
  • Again, what is the protection level of your package?

    -Jamie

     

    Monday, January 16, 2006 9:18 AM
  • I'm getting the same error:

    Error: 0xC002F325 at Transfer Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
    Task failed: Transfer Objects

    The package protection level is: EncryptSensitiveWithUserKey

    I've verified that my connection passwords are being saved by closing the package and re-opening it, then right-clicking the connection and picking 'edit', then 'test connection'.

    I'm guessing I have something misconfigured, but I just dropped the Transfer SQL objects object on the package, configured my source and destination, and set 'DropObjectsFirst', 'CopyData', 'CopyIndexes', 'CopyTriggers', 'CopyPrimaryKeys', and 'CopyForiegnKeys'.  Then I selected my objects.  However, that caused this error:

    Error: 0xC002F363 at Transfer Objects, Transfer SQL Server Objects Task: Table "Clients" does not exist at the source.

    Then, I just enabled 'CopyAllTables', 'CopyAllViews', and 'CopyAllStoredProcedures'.  That let me get through the 'table doesn't exist' error, and on to the 'cannot apply value null' error.

    If it makes a difference, my source database is SQL 2000, my target is SQL 2005.

    Thanks for any ideas you might have as to what I'm doing wrong.
    Monday, January 23, 2006 7:17 PM
  • how to check it?
    Wednesday, February 01, 2006 2:57 AM
  • Has anyone been able to find a solution?  I get the same error:

    [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

    Thursday, February 09, 2006 3:29 PM
  • AL C,

    What is the protection level of your package?

    -Jamie

     

    Thursday, February 09, 2006 3:40 PM
  • If you need to transfer all objects from SQL2000 database you could backup the database and restore it to SQL2005 server
    Thursday, February 09, 2006 4:14 PM
  • Jamie,

    TFYR.  It is set to "EncryptAllWithUserKey".

     

    Thursday, February 09, 2006 5:53 PM
  •  Al C. wrote:

    Jamie,

    TFYR.  It is set to "EncryptAllWithUserKey".

     

     

    Al,

    That means that all passwords are encrypted with a key relating to the user. Hence if the package is being run by a different user the passwords will not get decrypted and you might get the error that you're seeing.

    Are your packages getting run by a differrent user to that which created them?

    -Jamie

     

    Thursday, February 09, 2006 9:47 PM
  • There is a bug related to using SQL Server authentication at the destination connection in Transfer tasks. I guess, it will be fixed in the SP1 release.
    Friday, February 10, 2006 12:53 AM
  • A simple work around for this bug is to to use Windows authentication instead of SQL Server authentication at the destination.
    Friday, February 10, 2006 2:10 AM
  • No, I created the package and I am running it.
    Saturday, February 11, 2006 3:44 PM
  • In my case the destination is a remote web hosting service, so that wouldn't work for me, correct?
    Saturday, February 11, 2006 3:48 PM
  • I have the same problem as the thread originator. Backup and restore is not an option for me (and maybe the originator too) because the remote database is located at my service provider's site and I have no way of copying a backup file to a folder where the remote server can get at it.

    I tried copying the tables using the data export wizard from Management Studio. The data gets copied but I can't preserve the existing indentity column values - even though I enabled 'allow identity insert'

    I found the best solution was to call the import/export wizard from the Project menu inside an Integration Services project then manually edit the properties. This approach has the comfy old DTS feeling and seems to work without all the errors that management studio throws up.

    DP

    Monday, February 13, 2006 4:07 PM
  • Kaarthik Sivashanmugam, thank you for the post about the SA bug. I would have never expected to not be able to use the SA account to transfer objects between databases on the same database instance. Oh well... SA is no more....

    So, I switched to Windows authentication trying to execute the a SSIS copy database objects task. I am testing on the same database instance with account that is local admin for the server. I have everything in the task properties that relates to copying user accounts set to "False". I run the task, and it fails with the follwoing error message:

    Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "DROP LOGIN [{SERVER}\SQLServer2005MSFTEUser$
    {SERVER}$SQL2005]
    " failed with the following error: "Cannot drop the login '
    {SERVER}\SQLServer2005MSFTEUser${SERVER}$SQL2005', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


    I was very much surprised to find that after this failure all my windows accounts INCLUDING the system and network service were dropped from the database.

    Is there any good explanation for that?

    I have so far not succeeded to get the copy database objects task to run.
    Monday, February 20, 2006 3:11 PM
  • I am so suprise that Microsoft Sql server 2005 use SSIS to bulid transfer sql server object,and have some bugs.the transfer is fail and all windows accounts will be dropped. why? Sql Server 2000 's function 'transfer from Sql server to Sqlserver ' is very simply.why did MS give up?

    I don't like it

    Tuesday, March 07, 2006 7:25 AM
  • Just saw your post. I do not have any explanations on this behavior. Will post the results of my investigation soon.
    Tuesday, March 07, 2006 5:50 PM
  • Yes me too, this new SQL Server 2005 has so many bugs. The Export Wizard now does not work at all after applying the new SP.

    I suspect this may have something to do with those offshore development ideas those big boses have... Yes it is cheaper, yet they do not understand that quality may not be the same, so  at the end they will end up paying more.

    Just little bit out of the topic, some of my thoughts:)

    Thursday, March 30, 2006 11:06 PM
  • There was an issue reported on Transfer objects task attempting to drop the logins when the source and destination of the transfer reside in the same server. This happens when you set "CopyAllObjects=True OR CopyAllLogins=True" AND "DropObjectsFirst=True". It has been identified as a bug. Hopefully the fix will be made available soon.

    In your case, the problem will not happen if the source and destination are in different servers or if at least one of "CopyAllObjects", "DropObjectsFirst" is set to false.
    Friday, March 31, 2006 5:55 PM
  • What sort of issues are you having with Import/Export Wizard? Please report them to us and we can confirm if it is a bug. We can also potentially discuss work arounds if it turns out to be a bug.
    Friday, March 31, 2006 6:01 PM
  • I have noticed that even after applying SP1 the import/export wizard ignores my requests to delete rows at the destination table if I use 'optimise for many tables' during a data copy.

    The only way I have found of automating a refresh of all tables is to drop all the tables, create them all again and then do a data copy - of course all this can be rolled up into one ssis package but it's not exactly a quick and easy method.

    Ta,

    Mark.

     

    Thursday, June 08, 2006 1:26 PM
  • the following is a trace from a SQL2k box showing what SQL2005 object transfer is doing, this is giving me the error desribed earlier re missing tables,

    The tables in the source DB are owned by dbo, how ever this check passed through from SSIS seems to insist that only objects owned by the std (not NT) login can be copied, and therefore it returns that the objects don't exist.

    I too cannot migrate the source db to SQL2005 yet, and cannot (yet) use NT authentication,

    Is there a work around or hot fix for this?

    SELECT
    stbl.name AS [Schema],
    tbl.name AS [Name]
    FROM
    dbo.sysobjects AS tbl
    INNER JOIN sysusers AS stbl ON stbl.uid = tbl.uid
    WHERE
    ((tbl.type='U' or tbl.type='S'))and(tbl.name=N'tEvent' and stbl.name=N'Quant_DTS')

    Tuesday, August 08, 2006 12:01 PM
  • There seems to be issues with "Integrated Security".  And ... there are work arounds to make it work better.  We just enountered this issue & fixed it by creating a user on the remote system with the exact same credentials as the domain server.  We are also have a DB user with the same credentials as the windows security.  When you create the remote user you will need to create it with SQL security ... but use the same password that you have in windows.  If all the accounts are the same, you should be able to use "integrated security" ... even though your hosted server isnt part of your domain.  In our case, this is now letting us use SSIS to move objects out to the Hosted server.
    Thursday, September 28, 2006 10:24 PM