I am running a SSIS package that uses an Execute SQL Task to run a stored procedure that runs a select...for xml query then stores it in a variable called strSQL. I then use a Script Task in SSIS to write that variable to a file with a dynamically created filename.
The problem I am having is that the SQL Task takes around an hour to run. When I run the same stored proc in SSMS it takes less than a minute which makes me think that it is the size of the file being moved. If I let it run the end file is about 50mb. The Script Task only takes around 13 seconds to run. Is that to large for a SSIS variable? Is there another route to take for generating an XML file using SQL and SSIS?