Solved

Help With Generating A Large XML File using SSIS?

Posted on 2008-10-28
14
1,129 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

696 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