Solved

Help With Generating A Large XML File using SSIS?

Posted on 2008-10-28
14
1,114 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
  • 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now