I need to export/import the ASP.NET Membership data which is installed in my database via aspnet_regsql.exe. Does anyone have the Cliff notes on how to do this?
Thanks in advance.
Since this is just a set of standard SQL Server tables, you should be able to use any SSIS tool or technique to do this. The import/export wizard in SQL Server Management Studio should work just fine.|||That's what I was hoping, but unfortunetly that is not the case.
When I run the package created with the wizard specifying only the following tables:
aspnet_Users
aspnet_Applications
aspnet_Roles
aspnet_UsersInRoles
aspnet_Membership
aspnet_SchemaVersions
I get the following output:
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination 1 - aspnet_Membership [132]: The final commit for the data insertion has started.
Information: 0x402090DF at Data Flow Task, Destination 3 - aspnet_SchemaVersions [274]: The final commit for the data insertion has started.
Information: 0x402090DF at Data Flow Task, Destination - aspnet_Applications [28]: The final commit for the data insertion has started.
Information: 0x402090DF at Data Flow Task, Destination 2 - aspnet_Roles [222]: The final commit for the data insertion has started.
Information: 0x402090DF at Data Flow Task, Destination 4 - aspnet_Users [334]: The final commit for the data insertion has started.
Error: 0xC0202009 at Data Flow Task, Destination 3 - aspnet_SchemaVersions [274]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK__aspnet_SchemaVer__4316F928'. Cannot insert duplicate key in object 'dbo.aspnet_SchemaVersions'.".
Error: 0xC0202009 at Data Flow Task, Destination 2 - aspnet_Roles [222]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__aspnet_Ro__Appli__24E777C3". The conflict occurred in database "CommonPassDB", table "dbo.aspnet_Applications", column 'ApplicationId'.".
Information: 0x402090E0 at Data Flow Task, Destination 3 - aspnet_SchemaVersions [274]: The final commit for the data insertion has ended.
Information: 0x402090E0 at Data Flow Task, Destination 2 - aspnet_Roles [222]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 2 - aspnet_Roles" (222) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 3 - aspnet_SchemaVersions" (274) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0202009 at Data Flow Task, Destination - aspnet_Applications [28]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of UNIQUE KEY constraint 'UQ__aspnet_Applicati__3E52440B'. Cannot insert duplicate key in object 'dbo.aspnet_Applications'.".
Error: 0xC0202009 at Data Flow Task, Destination 4 - aspnet_Users [334]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__aspnet_Us__Appli__25DB9BFC". The conflict occurred in database "CommonPassDB", table "dbo.aspnet_Applications", column 'ApplicationId'.".
Information: 0x402090E0 at Data Flow Task, Destination 4 - aspnet_Users [334]: The final commit for the data insertion has ended.
Information: 0x402090E0 at Data Flow Task, Destination - aspnet_Applications [28]: The final commit for the data insertion has ended.
Error: 0xC0202009 at Data Flow Task, Destination 1 - aspnet_Membership [132]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__aspnet_Me__Appli__178D7CA5". The conflict occurred in database "CommonPassDB", table "dbo.aspnet_Applications", column 'ApplicationId'.".
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 4 - aspnet_Users" (334) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x402090E0 at Data Flow Task, Destination 1 - aspnet_Membership [132]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - aspnet_Applications" (28) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread4" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 1 - aspnet_Membership" (132) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination 5 - aspnet_UsersInRoles [387]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination 5 - aspnet_UsersInRoles [387]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - aspnet_Applications" (28)" wrote 1 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination 1 - aspnet_Membership" (132)" wrote 1 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination 2 - aspnet_Roles" (222)" wrote 1 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination 3 - aspnet_SchemaVersions" (274)" wrote 3 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination 4 - aspnet_Users" (334)" wrote 1 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination 5 - aspnet_UsersInRoles" (387)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package1.dtsx" finished: Failure.
The program '[5340] Package1.dtsx: DTS' has exited with code 0 (0x0).
Has anyone ever tried this before?
Thanks.
|||You need to ensure that you're running the packages in the correct order, so that the foreign key constraints aren't violated.|||The wizard creates a single package containing a single Data Flow Task. Inside the Data Flow Task is a pair of OLEDB Source and Destination Data Flow Sources. How do I control the order in which the OLEDB "pairs" execute?
Must I break each one of these out into its own Data Flow Task?
|||Yes. This default configuraion is running them in parallel; you need them to run in a specific serial order based on the FKs defined in the database, which cannot be done in a single data flow.|||Ok, thanks. That did it.
I appreciate your help.
|||Thanks for the feedback - good luck!
No comments:
Post a Comment