Solved

Help With Generating A Large XML File using SSIS?

Posted on 2008-10-28
14
1,120 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 36
export sql results to csv 6 36
Sql server get data from a usp to use in a usp 5 16
Passing Parameter to Stored Procedure 4 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
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.

803 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