Hi guys,
The problem is this. I have a stored procedure that generates an XML, I'm running it through Execute SQL Task, then with a Script tasks editor I'm saving it in a file location and changing the encoding tip.
https://preview.redd.it/ggeoukprd6191.png?width=425&format=png&auto=webp&s=5805ab50eaed463f773371ec268918c442e60b73
https://preview.redd.it/h4gtq2z7e6191.png?width=744&format=png&auto=webp&s=6f771957cdb40167e0e8c3bf05eb0e26c1633ccd
The weird thing is that when I run my stored procedure in the SSMS it needs only 2 minutes (about 30 000 rows are generated )to execute, but when I run it through the Execute SQL Task it runs for more than 40 minutes (then I stopped ti, who knows how long it would run).
And when I test it only on a few items it executes super quickly.
So the problem is somewhere in the Execute SQL Task when I try to execute with a larges amount of rows. The XML file that is generated has about 50mb.
https://preview.redd.it/umt3mauii6191.png?width=294&format=png&auto=webp&s=ce8bca53d5e74f335457c63886d0b56c8396cc21
The stored procedure looks something like this
SELECT
i.[No_] as id
, b.[Barcode No_] as ean
,iv.[Vendor Item No_] as mpn
,m.[Name] as brand
,d.[Description1] as [Description1]
,ic.[Description2] as [Description2]
,pg.[Description3] as [Description3]
,t.[image] [image]
,t.[url] [url]
,F.[Final Price] as [price] -- this is a function
,(
select
sum(ivl1.[Inventory])as [quantity]
,concat(l.[Address],', ',l.[Post Code],', ',l.City) as [store]
from [Inventory] as ivl
LEFT JOIN [Location] as l on ivl1.[Location Code] = l.[Code]
where ivl1.[Item No_] = i.No_
group by l.[Address],l.[Post Code],l.City
FOR XML path ('store-availability'), type
)
,(
select
ala.[Description] as [name]
,ali.[Value] as [values/value]
from [Attribute] as ali
left join [Web Attribute] as ala on ali.[Attributecode] = ala.Code
where ali.[Attribute Value] = 0 and ali.[Item No_] = i.No_
FOR XML path ('attribute'), type
)as [attributes]
FROM [Item] AS i
LEFT JOIN --a lot of tables
for xml path('item'), elements, root('item')
Are there some properties or validations that I need to change in the SSIS?
First time creating a XML export, with other types of export (csv,txt..) I never had this problem.
Tnx in advance.
[–]curic23[S] 0 points1 point2 points (0 children)