CODEWITHSUNDEEP
sql-serverssisdynamics-crmdynamics-365kingswaysoft
Sahil Tiwari
Sahil Tiwari

Reputation: 169

Moving SQL server data into Dynamics 365 CRM

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

Upvotes: 0

Views: 1024

Answers (2)

john McTighe
john McTighe

Reputation: 1181

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!

Upvotes: 1

Arun Vinoth PrecogTechnologies
Arun Vinoth PrecogTechnologies

Reputation: 22836

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.

Upvotes: 0

Related Questions