Solved

Help With Generating A Large XML File using SSIS?

Posted on 2008-10-28
14
1,146 Views
Last Modified: 2013-11-10
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?
0
Comment
Question by:scottmolitor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22824987
Why you are saving the data into a SSIS variable?!

Why you dont do all the work inside a dataflow directly from source to destination?!
Regards!
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22825088
If possible, go directly from SQL to XML Source using a Data Flow task in SSIS.
0
 

Author Comment

by:scottmolitor
ID: 22825271
I have a stored proc that I want to run everynight that looks like this:

SELECT
      title as "title"
      ,job_id as "job-code"
      ,source as "job-board-name"
      ,job_url as "detail-url"
      ,category as "job-category"
      ,description as "description/summary"
      ,fulltime as "description/full-time"
      ,parttime as "description/part-time"
      ,salary as "compensations/salary-range"
      ,city as "location/city"
      ,state as "location/state"
      ,company_name as "company/name"
FROM @Feed2
FOR XML path ('job'), ROOT('jobs')

This returns an XML link.  If I use an OLEDB Source or ADO source and execute that stored proc how can I output that to a new XML file everyday with a name based on date?  I am guessing we could use a Flat File Destination?  How could I configure a data flow task like this?  
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 22

Accepted Solution

by:
PedroCGD earned 250 total points
ID: 22825299
1. Create a dataflow to your controlflow
1.1 Add a source and add your query to that
1.2. Add a destination (FlatFile Destination) to insert all rows.

2. Create a Script task to rename your flatfile created in the previous step.

Helped?
regards
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22825690
Pedro has good advice, look here also for more info from Jamie Thomson.
http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx
 
0
 

Author Closing Comment

by:scottmolitor
ID: 31510861
thanks, I didn't think it would work like that.  I used an expression on the flat file destination for the file rename.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22830912
Yes! Use expression to rename the file or the script if you need to make file system operations like move or delete or copy!!!
Good!!!
Regards!!

Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:scottmolitor
ID: 22832088
Well this returns a XML document but it is not formatted correctly so it can't be opened because of all the errors.  Is there something I can change in the flat file destination to format it to XML?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22832147
oohh... you need destination in the XML format?
Could be a GAP of MSFT... because there isn't any XML destination... Only using Script Task Destination you could try to maintan XML format... I will check and give you some feedback!
Regards!
0
 

Author Comment

by:scottmolitor
ID: 22832265
Pedro,

That was the problem I was having before using the script task to make an XML document.  I had to store my XML output as a variable to bring into the script document and that took forever.  I am playing around with the XML task on the control flow to see if I can manipulate the XML file i created with the flat file destination to give it proper formatting.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22832319
OK...
could you attach the XML file here?
Which transformation are you doing into the original XML file?
regards
0
 

Author Comment

by:scottmolitor
ID: 22832358
I can't attach the unformatted XML file here because it is very large, (the reason I am having such a problem)  And I can't use any XML task in SSIS because it is so unformatted.  With the flat file destination it basically is 1 huge text file with no proper breaks.  So I might try breaking up my XML file creation then using an XML task to merge them but that seems very cumbersome.  I had a recommendation to use the bcp utility to create the XML file and call it with a File Process task in SSIS.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22832408
BUt which erros are you having using Flat File Destination?
Avoid use variables, mainly if the file is very large...
Regards
0
 

Author Comment

by:scottmolitor
ID: 22832684
It isn't a proper XML file using the flat file destination.  It is a text file with an .xml extension.  You can't open the file because it's not really an XML file.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question