0

I have more than 500s of tables in SQL server that I want to move to Dynamics 365. I am using SSIS so far. The problem with SSIS is the destination entity of dynamics CRM is to be specified along with mappings and hence it would be foolish to create separate data flows for entities for 100s of SQL server table sources. Is there any better way to accomplish this? Data flow

Control flow

enter image description here

enter image description here

I am new to SSIS. I don't feel this is the correct approach. I am just simulating the import/export wizard of SQL server. Please let me know if there are better ways

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Sahil Tiwari
  • 159
  • 2
  • 12
  • the container selection in last screenshot isn't package, it's the for-each container name of control flow – Sahil Tiwari Oct 24 '18 at 19:01
  • I feel SSIS is better approach.. Check this http://www.threadpunter.com/dynamics-365/dynamics-365-data-migration-using-sql-server-ssis/ it might help you – CR241 Oct 24 '18 at 21:16
  • For single table destination entities of CRM, it is good. For multiple entities, it's a nightmare. I realize it will be done by third part tools or if you want to save money, spend months writing code for relevant mappings of SQL DB tables to CRM – Sahil Tiwari Oct 25 '18 at 15:49

2 Answers2

1

It's amazing how often this gets asked!

SSIS cannot have dynamic dataflows because the buffer size (the pipeline) is calculated at design time (as opposed to execution time).

The only way you can re-use a dataflow is if all the source to target mappings are the same - Eg if you have 2 tables with exactly the same DDL structure.

One option (horrible IMO) is to concatenate all columns into a massive pipe-separated VARCHAR and then write this to your destination into a custom staging table with 2 columns eg (table_name, column_dump) & then "unpack" this in your target system via a post-Load SQL statement.

I'd bite the bullet, put on your headphones and start churning out the SSIS dataflows one by one - you'd be surprised how quick you can bang them out!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
john McTighe
  • 1,181
  • 6
  • 8
0

ETL works that way. You have to map source, destination & column mapping. If you want that to be dynamic that’s possible in Execute SQL task inside foreach loop container. Read more

But when we are using Kingswaysoft CRM destination connector - this is little tricky (may or may not be possible?) as this need very specific column mapping between source & destination.

That too when the source schema is from OLEDB, better to have separate Dataflow tasks for each table.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168