2005 SSIS issue

moondaddy

unread,

Nov 18, 2005, 1:05:43 AM11/18/05

to

I'm new to SSIS and have not been able to import a database from sol 2k onto
2005, however, I did manage to restore a backup from 2k into 2005. Now I'm
practicing creating a SSIS package to copy all objects and data from this db
in 2005 (db1) into a new db in 2005 (db2).

I added a Transfer SQL Server Objects Task into the Control flow window and
set the appropriate source and destination databases etc. I double clicked
on it and set most of the props to true and everything looked pretty good.
I clicked on OK so I was back at the Transfer SQL Server Objects Task. Then
I right clicked and selected Execute Task. it only ran about 1 second and
in the execution results window I saw the error:

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

Where can I find this property called 'Login'?

Is there anything else you can think of that I need to do to make this run?

Thanks.
--
moon...@nospam.nospam

Peter Yang [MSFT]

unread,

Nov 18, 2005, 3:43:00 AM11/18/05

to

Hello Moon,

Based on my experience, this issue could occur if the logins in SQL 2005
server and original SQL 2000 server is different.

My suggestion is that you transfer logins between the servers before you
try to import datqabase again.

SQL 2K transfer login article does work for SQL2005 since sp_addLogin is
still supported.

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/default.aspx?scid=KB;EN-US;246133

TransferLogins task in SSIS (DTS) to no longer transfers the SQL Passwords.
From the BOL, the SQL login is transferred, but a random password is
assigned. This requires the DBA to reset the password for each SQL users.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

moondaddy

unread,

Nov 19, 2005, 1:51:09 AM11/19/05

to

Thanks for all the info!

However... I've really had it with this thing. how difficult can it be to
run a DTS, import or what ever you want to call it, from one db to another.
I've spent a day+ using the Best Practice Analyzer and resolving ALL issues,
ran checkDB, ran maintenance plans to clean things up and it didn't work. I
removed all users except dbo (sa) and users which cant be removed. then I
backed it up and restored it on 2005. then created a SSIS package using the
Transfer SQL Server Objects control and I still get the error:

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

I tried all combinations of importing users, logins, etc from the old db to
the new 2005 db and still get the same error.

FINALLY, I created 2 new databases in sql 2005, and added a single table to
dbA. Then I created a new SSIS project and package, added a Transfer SQL
Server Objects control, and then once again tried all combinations of adding
tables, users, logins, etc, and I STILL get the error:

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

I CANT BELIEVE I'M THE ONLY ONE GETTING THIS PROBLEM. Why is this so
troublesome? Since this 'Show Stopper' happens no matter what I do and no
matter what/where the databases are, then there should at least me some
documentation on this.

PLEASE HELP! Anyone....

Thanks.

--
moon...@nospam.nospam
"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message
news:NVqzXwB7...@TK2MSFTNGXA02.phx.gbl...

moondaddy

unread,

Nov 19, 2005, 3:48:02 AM11/19/05

to

Getting no where fast. I found a post on Google where a company had a
similar error. change changed the security from sql to windows on the
server and then things worked for them. I did that and corrupted the server
because the databases (master, temp, etc.) where probably still expecting
sql - sa. So I uninstalled sql server (not SSIS, etc.) and re-installed it
and this time used windows security. (don't know if this introduced
corruption) Then I created 2 new databases on sql server 2005; test1 and
test2. in test1 I created a table called customers and 2 columns, name and
id. then I went into SSIS, created a project and package, and a Transfer
SQL Server Objects Task control.

At least this time it ran... but that's because nothing was selected to
import (but at least it ran right?). Next on only one property I selected
copy all tables and after it hung for about 3 minutes I got the following
test in the output window:

SSIS package "Package.dtsx" starting.
Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server
Objects Task: Execution failed with the following error: "ERROR :
errorCode=-1071636471 description=An OLE DB error has occurred. Error code:
0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "Invalid object name 'dbo.Customers'.".
helpFile=dtsmsg.rll helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Task failed: Transfer SQL Server Objects Task
SSIS package "Package.dtsx" finished: Success.

so in short it complained about "Invalid object name 'dbo.Customers'.".

I hope there's some good (helpful) advise out there....

Thanks.

--
moon...@nospam.nospam
"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message
news:NVqzXwB7...@TK2MSFTNGXA02.phx.gbl...

Peter Yang [MSFT]

unread,

Nov 21, 2005, 3:39:37 AM11/21/05

to

Hello,

I was able to reproduce the issue on my side. I created a simply project to
transfer two tables from Northwinds to a new database on SQL 2005.

If I set both connectors to Windows Authentication, the package works
fine.If I set the SQL 2000 connection to SQL Authentication and the SQL
2005 connection
to Windows Authentication, the package works fine.

If I set the SQL 2005 connection to SQL Authentication regardless of the
SQL 2000 connection type, the package fails with this error:

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

I found this issue was just reported to product team recently and if there
is any update on this, I will let you know.

As for the new error, it seems that you did not select "copyschema" in the
dstination options. You may try to check this and "copydata", and then
select the table collection you want.

Please let me know if you have further questions on this.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670

| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670

| X-RFC2646: Format=Flowed; Original
| Message-ID: <e99v4XO7...@TK2MSFTNGP11.phx.gbl>

| Newsgroups: microsoft.public.sqlserver.dts
| NNTP-Posting-Host: cpe-24-175-64-176.houston.res.rr.com 24.175.64.176
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl

| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.dts:62120
| X-Tomcat-NG: microsoft.public.sqlserver.dts

Ches Weldishofer

unread,

Nov 21, 2005, 2:42:27 PM11/21/05

to

I've found what seems to be a similiar issue.

Trying to copy just one table from a transactional system into a
warehouse staging system.

When using SQL 2000 I can transfer the table with no issues whether it
be with SQL Authenticate on both sides or windows on both or any
combination. However trying to use SSIS, I get different errors
depending on where it's run from.

Runing SMS 9.00.1399.00 locally and on the server

Executing the SSIS from Visual Studio:
Error: 0xC002F363 at Transfer XLATTABLE, Transfer SQL Server Objects
Task: Table "XLATTABLE" does not exist at the source.

Executing from Server Management Studio (run locally):
Error: The task "Transfer XLATTABLE" cannont run on this edition of
Integration Services. It requires a higher level edition.

Executing from Server Management Studio (Termed into server):
Error: Table "XLATTABLE" does not exist at the source.

I need to move a total of 30 tables out of 16,000, moving the entire
database is unacceptable. Because we're going from transactional to
reporting.... Moving all the logins is not required and would give way
to much access to way to many people.

The only thing I can see at the moment is to run a 2000 DTS to move the
tables, then pop back into SSIS to complete the work that needs to be
done on them. This isn't really acceptable either.

Any clues or suggestions?

Ches Weldishofer
Clear Channel Communications