July 7, 2010 at 9:55 am
I am using the 2008 BI Studio to create a job to grab a table from a 64 bit SQL2005 DB to a 32 bit SQL2008 server. It connectgs fine, i think, and fails at the data import. I am using a Transfer SQL Server Object task. I am not new to SQL server but I am new at using SSIS. I have created many DTS packages for SQL2000 though.
OUtput window Results:
SSIS package "GetMCSInfo.dtsx" starting.
Information: 0x4002F418 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Logins to transfer.
Information: 0x4002F41D at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Users to transfer.
Information: 0x4002F362 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Rules to transfer.
Warning: 0x8002F364 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Table "[dbo].[AutoRestore_MCS]" already exists at destination.
Information: 0x4002F370 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Views to transfer.
Information: 0x4002F358 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Stored Procedures to transfer.
Information: 0x4002F374 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no User Defined Functions to transfer.
Information: 0x4002F378 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Defaults to transfer.
Information: 0x4002F382 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no User Defined Data Types to transfer.
Information: 0x4002F386 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Partition Functions to transfer.
Information: 0x4002F390 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Partition Schemes to transfer.
Information: 0x4002F394 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no Schemas to transfer.
Information: 0x4002F398 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no SqlAssemblies to transfer.
Information: 0x4002F402 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no User Defined Aggregates to transfer.
Information: 0x4002F406 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no User Defined Types to transfer.
Information: 0x4002F410 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: There are no XmlSchemaCollections to transfer.
Information: 0x4002F41E at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Truncating table "[dbo].[AutoRestore_MCS]"
Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "Version90 database compatibility level is not supported.".
Task failed: Transfer SQL Server Objects Task
SSIS package "GetMCSInfo.dtsx" finished: Success.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
July 7, 2010 at 9:56 am
BTW - both DB's are at 90 Compatibility.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
July 7, 2010 at 10:12 am
You might just use the Import/Export wizard and save the package for a single table..
DTS 2000 experience is good, but it only helps a little in SSIS which does almost everything differently.
CEWII
July 7, 2010 at 10:24 am
Is your requirement to move data or to move schema info (or both)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 7, 2010 at 10:52 am
I only need the data.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
July 7, 2010 at 10:56 am
Then you should forget the Transfer SQL Server Objects task. Look instead at the Data Flow task where (at its simplest) you define a source, a destination and a mapping between the two.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 7, 2010 at 11:03 am
I will look at it.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
July 7, 2010 at 3:12 pm
I'm with Phil, thats all you need, and it is EASY to setup.
CEWII
July 9, 2010 at 1:58 am
I have a similar problem, but I need to transfer data and table structure (which changes frequently as new fields are added or existing ones modified). The Transfer Objects task was perfect as I could use it to drop the table and recreate it (with new fields) and then transfer the data, all in one step. The SSIS package I had under SQL Server 2005 worked flawlessly doing just that. Now the destination server was upgraded to 2008 and the same package fails with the infamous "Version90 database compatibility level is not supported" error. Both databases are Version 90. The only difference is that the destination is now on a SQL 2008 server while the source remains on a SQL 2005 server. I tried it with x86 and x64 SQL 2008 servers with the same results. I can't upgrade the source SQL 2005 server to 2008.
Any ideas how I can make this work?
November 23, 2010 at 3:49 am
Hello sb-619473,
I am in exactly this position, could you please let me know what happened in the end? Did you manage to get round it?
Regards,
D.
November 23, 2010 at 3:55 am
Where is the SSIS package running? On 2008 or 2005? I would expect it to work if using a 2008 package (though haven't tried it).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2010 at 5:12 am
Hello,
Its running on the SQL 2008 R machine.
Regards,
D.
November 23, 2010 at 5:58 am
And the problem you have is just with the 2005 database, or with both? Are your db's both set at 90?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2010 at 7:06 am
The 2005 database is set to 90, but what other database?
Regards,
P.
November 23, 2010 at 7:15 am
The rest of this thread is about transferring objects between a 2005 database and a 2008 database - that makes two databases. So I mean the other one, presumably on 2008 and also presumably set at 90 or 100.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply