I have some questions responding to Datamigration to MSCRM.
Does someone have experience with that procedure?
My question is related especially to the SSIS.
Thx
Ok, I describe my wishes.
I have got one database with some tables. That is my source DB.
Now I wanna migrate this data from source DB to a dest. DB.
But I need to transform the data cuz the structure of these tables are not the same.
For Example:
source tables: 1st table: id | name | idlastn
2nd table: id | lastname. (id and idlastn are in relationship)
dest tables: id | name | lastname.
So i have to move data to dest table with the assumption "Where 1sttable.idlastn = 2ndtable.id" in the source DB.
|||SSIS should be able to handle that wihout any problems. Are you having difficulties setting it up, or just looking for validation that it is possible?|||Actually I have difficulties to find the right component in SSIS for this kind of migration. What do I put between Source and Destination Table, plus I have two source tables (in the same database) and one destination table (in other DB, what is not important)?
Please, can you help me?
Thx for all
|||
Dear Smilebey,
Send me the CREATE Table statment of your tables in source and destination to pedro.perfeito@.cgd.pt.
I will try to help you, and give you the better option to resolve it!
Thanks!
|||
PedroCGD wrote:
Dear Smilebey,
Send me the CREATE Table statment of your tables in source and destination to pedro.perfeito@.cgd.pt.
I will try to help you, and give you the better option to resolve it!
Thanks!
Actually, Pedro, that's what we do here in the forums... We try to help individuals here so that others can see the results.|||
Ok, I describe my wishes.
I have got one database with some tables. That is my source DB.
Now I wanna migrate this data from source DB to a dest. DB.
But I need to transform the data cuz the structure of these tables are not the same.
For Example:
source tables: 1st table: id | name | idlastn
2nd table: id | lastname. (id and idlastn are in relationship)
dest tables: id | name | lastname.
So i have to move data to dest table with the assumption "Where 1sttable.idlastn = 2ndtable.id" in the source DB.
You can put two OLE DB Source components in one data flow, and then use two sort transformations to sort them each by their ID. Then hook the two flows up using a MERGE JOIN transformation followed by an OLE DB Destination.|||
Dear Philp,
You are right! But I always describe the solutions for my problems and the others problems. I created a blog just for show to all the people the solutions and your important help of MSDN Foruns!
So, smilebey, write here the CREATE Table statments in order to all comunity try help you, including me!
Thanks Phil for the comment!
Regards!!
|||Sincere thanks are given to all.First I want to apologize for my late post and answer. I was busy with school stuff BUT
I solved my problem in the MS SQL Management Studio. I copy the relationed appreciable rows in one table. For every relevant relationship.
For instance:
source tables: 1st table: id | name | idlastn
2nd table: id | lastname. (id and idlastn are in relationship)
So, I make a table with following content:
id | name | idlastn | lastname.
Now I haven't to do that with SSIS.
But there is an other problem and I'm despaired.
I migrate my tables in flat files. Now I want to imort the data from flat file to destination DB.
But there are so many problems. The Error list:
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Kunde" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [1]] Error: The "output column "Kunde" (84)" failed because truncation occurred, and the truncation row disposition on "output column "Kunde" (84)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [1]] Error: An error occurred while processing file "C:\Dokumente und Einstellungen\sma\Desktop\iso_tables\Kunden.txt" on data row 33.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
What I have to do with the row Kunde?
TIA
|||
Smilebey,
Check the lenght of your fields inside the dataflow and the destination database table...
the problem is here...
regards!!
|||Thx Pedro, I know it's about the length of the fields but what can I do. The dest tables are unchangeable. So what do you think, what I have to do with the source tables?TIA
|||
Tell me the format of the column Kunde in the source and destination.
If you cant change the destination, you must change in the dataflow...
Regards!
|||OK, I upload two pics.The first one is showing the format of Kunde in source table, the other one the column Name (dest column of Kunde).
Thx
|||So there's no exclamation point in OLE DB Destination because i put the Data Conversion between Source and Dest. DB. I convert Kunde in an type with length of 50.
But the errors are the same. I copy the list in this post again.
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Kunde" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [1]] Error: The "output column "Kunde" (84)" failed because truncation occurred, and the truncation row disposition on "output column "Kunde" (84)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [1]] Error: An error occurred while processing file "C:\Dokumente und Einstellungen\sma\Desktop\iso_tables\New\Kunden.txt" on data row 33.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
TIA
|||Instead of a Data Conversion (which will throw an error on truncation), use a Derived Column, and the SubString function to explicitly truncate the input string.
