146 How to import multiple xml files to sql server using ssis

[@simmi641]

Thank you so much for all your videos really helpful ❣️❣️

[@learnssis]

You are most welcome Simran.

[@TKJ]

I want to fetch data from 80 different tables and load to 80 different tables. For that I have to create 'OLE DB Source ' and 'OLE DB Destination ' 80 times???? Any alternative solution to load from one source and destination only?

[@learnssis]

Take a look at this video kzbin.info/www/bejne/j4nIdo2pfNJ9qKs

[@manikyakalyan2228]

Hello, I am getting error The "XML Source" failed because truncation occurred, and the truncation row disposition on "XML Column length is nvarchar 350. But Data from xml is more than that. I can see in advance editor window max size is 255, after changing to 500 it is again showing 255. Please help me in this regard. Thanks in advance.

[@learnssis]

Can you pls take a look at this ? It also asks to change the external and output columns in xml source, please try once. stackoverflow.com/questions/4937055/xsd-how-to-code-string-length-255

[@ClaUnerer]

Hello sir, thank you for the nice and helpful tutorial! I have a question, would it be possible to make some tutorial for how to import multiple multilevel xml files? Personally, I've been struggling with this for a few days now. I have several xml files that have nested elements and I would like to import values from these xml into different tables in SQL so that I can create relations between them (for example add some index column)?

[@learnssis]

Thank you. Sorry I never tried importing multilevel xml files to sql server. I have only imported the normal xml files similar to the one I shown in this video.

[@piyushamohanty2671]

This really helps me. I wanted to know is there a way the same multiple XML source file will be merged into one single variable.

[@learnssis]

Sorry I am not sure if it can merged to a single variable.

[@venkatkavi0822]

Hello Sir, I stuck with below situation. Consider that , I have a 3 txt files, each file has a two columns with space as delims. Don't have a column names. I have a sql server table with 6 columns Now, I want to extract those all columns from text files and insert into sql server table using ssis package. Note that , each file has a different row counts. Thank You.

[@learnssis]

You can read the data from each flat file using a flat file connection manager with flat file source, and then using Union All you can combine the data from 3 flat file source into a single source with 6 columns and can insert it into sql server table.

[@venkatkavi0822]

@@learnssis I tried, but inserting two columns below two columns. I want next to each other

[@learnssis]

@@venkatkavi0822 if you have 2 columns in one flat file and if you will combine them with another flat file containing 2 columns, then it will become 4 columns.

[@venkatkavi0822]

@@learnssis Sir, if possible could you please make a video on this?

[@learnssis]

@@venkatkavi0822 There is already a video on Union All kzbin.info/www/bejne/l5TMd4uKobyqgNE

[@giria7809]

Hi Akhil Anna, I have sample2 and Test2 tables in sql server. If any changes(like insert, update, delete) happen in sample2 table then those changes will be updated to Test2 table. How to do it using SSIS Anna. Can you please tell me the solution Anna. . Thanks for Advance

[@learnssis]

I think the easiest method to do this will be to use a trigger for each operation. Like there will be a trigger on insert, another trigger on update and third trigger on delete and then we can capture the information using the triggers and store it in a staging table first or just directly update it to second table if the second table is also on the same server.

[@giria7809]

Yes Anna, Trigger is working. But I want Solution in SSIS Anna. I have a requirement Anna. They won't need Triggers. That's why I am asking. If you give a solution in SSIS. It is very helpful Anna.

[@learnssis]

@@giria7809 There is no such component in SSIS which can do this. You would need to use SQL here. If you are not using triggers then you have only 3 options remaining. 1. truncate and repopulate the whole second table from first table every time. But this can be time consuming if source table is huge. 2. Can you check the temporal table concept in sql server ? I have created a video on this one. See if you can create the source table as temporal table then it will contain the history of all insert\delete\update along with date and you can easily fetch the new\updated\deleted records from it kzbin.info/www/bejne/kH69n4ejlL9rrck 3. Calculate the hash on all columns except Id from source table and store it in a table. now calculate the hash on all columns except id on destination table and store it in a staging table as well. Now compare both the staging tables based on a join on id where a.hashcolumn != b.hashcolumn This way it will return all updated records. Now you can copy all id from source table move to a new table in the destination database, and check what are the id those exists in destination table but not in source table , this will give you deleted records. you can delete them from destination table. You can do this using left join. Now check what are the id those exists in source table but not in destination table , this will give you inserted records. you can take those id back to source and select all columns for those id and insert into destination table.

[@giria7809]

@@learnssis Thank you so much Anna. . Your Response is unbelievable. I am really proud of you as a Learn SSIS Subscriber. .

[@learnssis]

@@giria7809 No problem.

[@jagadishm8264]

Akhil Why we are creating XSD files

[@learnssis]

XSD file is required by SSIS to know about the schema and structure of the XML files.