SSIS Transfer Object Task

  • 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. **

  • 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. **

  • 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

  • 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

  • I only need the data.

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • 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

  • I will look at it.

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • I'm with Phil, thats all you need, and it is EASY to setup.

    CEWII

  • 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?

  • 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.

  • 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

  • Hello,

    Its running on the SQL 2008 R machine.

    Regards,

    D.

  • 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

  • The 2005 database is set to 90, but what other database?

    Regards,

    P.

  • 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