Solved

Help With Generating A Large XML File using SSIS?

Posted on 2008-10-28
14
1,142 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

729 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