-1

I need to read the .dat file (each row contains different columns) and insert into SQL Server Database Tables (insert parent (2nd row and few more rows based on first column value) and then remaining child records). Please suggest the solution. First row contains file name and generated date.

The test DOC file is as below:

HDR~GO_123_ROS_20111004_00189794.DAT~20111003 044800~123~20111004 045101~20111004 023659~1
PRA~1234567~20110629~20111110~10602402~MISS~Bloggs~Laura~Claire~~~~F~19880612~JH342567C~~~20110531~~A~~~~~~~~~~~~~~~Ex-employee and 

Applicant~LAURA.BLOGGS@NHS.NET~20010226~~20111110 112826~~~~~
PHA~1234567~15691675~H1~1743762397~20110704~~20111109 160911
STA~1234567~2075698~20090420~~NHS_REC_CHECKLIST~~~~~~~~~~~22/04/2009 00:00~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~20090427 115502
ADA~1234567~9028057~HOME~GB~Yes~The Old Engine House~Wind Park~Wind Hill~Hatbury~SHR~SS1 2AA~GB~20110704~~20111109 160911
ASA~1234567~936458~20110601~47121231~20060727~E~10602402~ACTIVE_ASSIGN~Active Assignment~X~123 Monthly~Calendar 

Month~96867~Y~1117262~~~~~B~Y~0~W~37.5~0~~328245~4741068~6971867|AHPs Maternity Therapy Assistant Bank|S9C|Bank|~NHS|XR02|Review Body Band 2~XR023~~~Additional 

Clinical Services|Helper/Assistant~Default Home|Standard User||~||~~~~~~~~~~~~~
ORA~128111~070 6 Domestic (BRO)~NHS_TM~19510101~~~~~070E642~548444~~96804~20061019 233601~070E642
LCA~552921~070 z Maternity LWH~Maternity Town Hospital~~Maternity~Town Wells Hospital~~~~~GB~~~Y~~~~~~~20110803 161310
ETA~1234567~17453990~NHS CRB and Vetting Checklist~NHS CRB and Vetting Checklist~~~~~~~~~~Enhanced~10/07/2006 00:00~02/01/2007 00:00~1150189058~Post To Be 

Offered~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~20111231 064907
ETA~201346~957384~ NHS Regs and Memships~NHS Regs and Memships~Nursing and Midwifery Council~ 94I0864E~ 31/08/2012 00:00:00~~ 31/08/2013 

00:00:00~~~~~~~~~~~~~~~~~~~~~~~~~~ Nursing and Midwifery Council~ Sub-Part 1 RNLD (Learning Disabilities)~~~~~~~~~~~~~No~No~No~No~~~~ 20110719 232041
CRA~1234567~4910890~7648535~SP~N~~~20100601~~20100610 163220
CMA~1234567~10005280~PERSONAL~ACHIEVED~NHS|KSF|Health, Safety and Security C3|Core~20091027~20130706~1 - Level 1~123~~~~20091101 070336
QLA~1234567~4698382~Bachelors Degree~Occupational Therapy~ATTAIN~02:01~20100707~~~City University~UK~20101018 142415
ABA~1234567~43247770~Annual Leave Hours 1~AL~~20110923~20111228~~20111230~~20121226~~20121226~~~~~~D~~~~~~~~~~20111010 150639~~~~~~~~~~~
TRA~1234567~43247770~MEL - Module 4 - Safeguarding Family - ALL CLINICAL STAFF~Training 

Leave~Withdrawn~~20130918~09:15~20130918~15:00~20130918~09:15~20130918~15:00~~~~~~~~~~~~~~~20130725 100052
COA~1234567~937490~5507703~20110308~47121231~ABC~~~0702K161~~~~~1~20110308 103538
POA~768853~19510101~47121231~901335~901335|Maternity Hospital Bank Place Qualified|N9A|Bank|~~7012170~Additional Clinical Services~Healthcare Assistant~N9A~
Dovydas Šopa
  • 2,282
  • 8
  • 26
  • 34
Mohan
  • 19
  • 3
  • 3
    How about you make some sort of attempt? – Mitch Wheat Mar 24 '17 at 04:47
  • `.dat` is a generic extension. It isn't associated with a specific format. You'll have to write a program to parse it. Where does this come from? What application? What is the file's specification? The source application probably has documentation that explains what each line means – Panagiotis Kanavos Mar 24 '17 at 07:55
  • Furthermore, you *can't* write directly to CRM's tables, just like you *can't* do that with *any* application. Not unless you wan to corrupt your data. Use its purpose-built interfaces that make sure business transactions are created and entered correctly – Panagiotis Kanavos Mar 24 '17 at 07:58
  • 1
    PS I hope that's dummy data and you didn't just expose a hospital patient's data? – Panagiotis Kanavos Mar 24 '17 at 07:58
  • Is this a standard NHS file? Why don't you check NHS' documentation for the format? – Panagiotis Kanavos Mar 24 '17 at 08:01
  • This is not standard NHS file rather I have created the equivalent one. Actually, I just want to add/update into Staging DB tables using SSIS and not in dynamics CRM directly.Each row of test file will go to various staging tables based on first column value(PRA-->tableA, PHA-->tableB and like wise) – Mohan Mar 24 '17 at 13:39

1 Answers1

1

If you are using CRM Online as you've specified in your tags, you cannot write directly to the CRM tables. You must use an extension such as Kingswaysoft which will write to the CRM entities via the exposed web services.

http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365

jasonscript
  • 6,039
  • 3
  • 28
  • 43