New answers tagged xml
Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...
Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.
Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.
You just need to specify the first SiteInfo node using  update [dbo].[Sites] SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo)') where Siteid = 1
I get the same error when I do this: DECLARE @foo VARCHAR(32) = CHAR(0); -- 0x0000 SELECT @foo FOR XML PATH, TYPE; So, find all instances of DescFuncao or DescTempoExperiencia (sorry, you don't use table aliasing, so it's impossible to tell which table they come from) where the contents contain CHAR(0), and fix them. For example: UPDATE dbo.whatever ...
The line: ...SELECT [CodFuncionario],STUFF(( SELECT ' / ' + CAST([DescFuncao] + '-' + [DescTempoExperiencia] AS VARCHAR(MAX)) Should be: ...SELECT [CodFuncionario],STUFF(( SELECT ' / ' + CAST(replace([DescFuncao] + '-' + [DescTempoExperiencia],char(0),'') AS VARCHAR(MAX))
You should shred on /WEBSITES/WEBSITE in your second query as well and use a predicate against the NAME node in the values clause and then get the value from VALUE node. select S.X.value('(WEBSITE_ID/text())', 'nvarchar(100)') as WEBSITE_ID, S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text()) eq "COST"]/VALUE/text())', ...
Top 50 recent answers are included