| Author |
Topic |
|
JeReLo
Starting Member
16 Posts |
 Posted - 2008-02-16 : 00:52:53
|
| I am having trouble with the SSIS wizard results. The connections to source and destination test good, but when I execute a task to copy all tables from a hosted OLEDB to a local SQL native connection, I get this error:Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "eventID". (SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task: Column metadata validation failed. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination - diary" (25)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard) The actual table that it is trying to write to is empty. What might I need to do, in order to correct the situation?thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-16 : 01:33:12
|
| Are the source & destination tables correctly selected?Is EventID an identity column? |
 |
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 01:44:11
|
| EventID is an identity column, yes. If this is causing the error, how could I overide this, as I want to copy the identity column of the source to the dest. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 02:08:20
|
| And further to your question, yes I believe the source & destination tables are correctly selected. There are just three tables in the source, and the same three tables are selected in the destination.What are the workarounds when copying an identity column data... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-16 : 02:11:27
|
| Had you checked 'Enable identity insert' property while configuring Export/Import wizard? |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 02:15:08
|
| As I look at the properties of dataflow objects, I do not see this property. Where would I access it? |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 02:31:49
|
| I recreated the SSIS package, and then I'm given the option to edit mappings, and found the enable identity insert option. Also found options to drop, and to delete.After this, the task executed without error. However, the destination tables are still null. I have confirmed that the source has data, so why wouldn't it carry over the data??? |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 02:51:12
|
| I tried a new SSIS package, and had it create a new database this time. It ran fine, and it created the new database. But it has no tables! There must be some step I'm missing.When I set the transfer settings, I can set the destination schema name. Currently it is dbo, but it could be sys or information_schema. Would this setting be involved? |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 03:03:47
|
| Experimentation indicated that dbo was the appropriate setting. I was also able to preview the contents of the transfer (during the ssis package configuration) and it confirmed that there is data there. However after completing the wizard, executing the task, and then checking the refreshed table data on the destination, there is no data in the table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-16 : 12:37:12
|
| Are you creating the table as a part of transfer always? if yes, are you sure your default schema is dbo? if not, the tables you create will only belong to your schema only. And if the table coexists in dbo schema you will not see the data there. Can you reconfirm which table you are selecting before transfer process and make sure you look into right table. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-16 : 14:57:39
|
| I'm sorry I am probably unable to intepret you correctly, but I'll tell you what I can see, and hopefully you'll understand the answers to your questions. I checked the properties of the tables on the source and destination, and in both cases the schema is dbo. I'm not sure where to find the 'transfer always' information. I have confirmed that I am using the same tables in source and destination. This is a very small database, with just three tables, so it's easy to see that the table names are the same, and are connected in the dataflow panel.Can you tell me what to look for in order to explore if the 'table coexists in dbo schema' as this sounds like a promising area to troubleshoot. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-16 : 23:06:58
|
| They are not settings but what i was asking was to make sure you dont have same table existing under a different schema.b/w did you notice what the progress tab says while SSIS runs? Did it give any warnings/error messages? probably you will be able to get more info from progress tab when package is executing. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-17 : 02:48:10
|
| After completing the validation, and preparation for execution, it gets to 50% of the pre-execute phase and then generates 3 errors:[Destination 1 - pagevisit [85]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". [Destination 1 - pagevisit [85]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid. [DTS.Pipeline] Error: component "Destination 1 - pagevisit" (85) failed the pre-execute phase and returned error code 0xC0202025. Finally in summary:[DTS.Pipeline] Information: "component "Destination - diary" (25)" wrote 0 rows. [DTS.Pipeline] Information: "component "Destination 2 - worklog" (144)" wrote 0 rows.[DTS.Pipeline] Information: "component "Destination 1 - pagevisit" (85)" wrote 0 rows. I've done some searches on the errors, but they didn't turn up anything I could work with yet. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-17 : 03:30:26
|
| can you explain the steps you did in configuration of Export/import task? Are you sure you viewed the transformation tab and founds the columns to be correctly mapped? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-17 : 19:41:17
|
| The context is that I have a local SQL 2005 server, and a database called monitor, with three tables (pagevisit, diary and worklog). On a host, I have a SQL 2005 database called JeffLee_monitor, with tables of the same names. The hosted database has new data that I’d like to copy to the local server.I open SQL Business Development Studio, and create a new Integration Services Project. Under Project, I choose SSIS Import and Export Wizard. I select the data source, (which the hoster says should be OLE DB Provider for SQL) enter the server name, user name, password and database name (JeffLee_monitor). After clicking next, I enter the destination. I use SQL Native client, enter the server name, use SQL Authentication with an administrator account, and open the local database (monitor). I click next, and select “copy data from one or more tables or views”. After clicking next, I select source tables and views. I select all three tables, so that each source [JeffLee_monitor].[dbo].[diary] connects to the corresponding destination [monitor].[dbo].[diary] I choose edit mappings for each table, and select delete row and insert edit enable. After clicking next, it validates the selections, and all actions complete successfully. It creates Package1.dtsx under SSIS Packages, and I right click it and select execute package. The preparation sql task goes yellow and then green. The data flow task goes yellow and then red. Under the progress tab, I see the log, and the same errors occur that were identified earlier. When I check the destination database tables, they are all emptied (nulls). Since the errors only occurred with one of the three tables, I removed this dataflow task from the package, and ran it again. Then the worklog table was filled as expected. So now I’ve been able to narrow it down to one specific table called pagevisit.Then to troubleshoot, I created a new package with the same initial settings. In this case, I only selected the problem table, and in the “Select Source Tables and Views” section, I confirm that all the columns of the table are selected and that their fomat matches that of the destination. In preiview I confirm that the data exists on the source, by the line: SELECT * FROM [JeffLee_monitor].[dbo].[pagevisit] On executing the task, I get the same errors. [Destination - pagevisit [37]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".[Destination - pagevisit [37]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.[DTS.Pipeline] Error: component "Destination - pagevisit" (37) failed the pre-execute phase and returned error code 0xC0202025.At this point, I’m wondering what the OLE DB record is, which is referenced in the first error. And secondly, how I can look for the problem related to the ‘ OLE DB accessor ‘ and how to reset the metadata. |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-19 : 11:46:50
|
| Stab in the dark here, but in the pagevisit table, do you have an identity column set up? It might be barking because you are attempting to explicitly insert an identity value. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-19 : 23:58:01
|
| Stab in the dark is fine! I'm willing to try anything... However, I thought that because I set 'insert identity enable' that it was able to explicitly instert and identity value. I'm also deleting all the existing data, so I shouldn't have any duplicate entries. In answer to your question, yes, there is an identity column set up. Given the identity insert enable setting, would this be a problem.My guess is that it has something to do with the 'ole db accessor' which sounds like something related to accessing the database on the server. I've let the tech support people know, and they're looking at it. But evidently this is an obscure error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-20 : 00:18:52
|
| To be honest,i've never come across suich an error while using export/import task. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-21 : 01:12:36
|
| Many thanks for the support on this question. I will just delete the table and give up! It was useful for my learning, but not worth anymore of our attention...Case abandoned. |
|
|
|
JeReLo
Starting Member
16 Posts |
Posted - 2008-02-21 : 03:26:17
|
| I ended up creating a copy of the table on the hosted server, which worked, and allowed me to stop focussing on the server database as the problem. I created a copy of the table on the local server, and was able to copy the server table to it. This kind of confirmed that the problem was with the original local table. After deleting and recreating it, I was able to run the original SSIS package. So the 'column metadata' problem looks like 'corruption'. I never did learn what caused this corruption, but its gone now.Case solved. |
|
|
|
Next Page
|
Validation errors