Solved

XML Export: SSAS?

Posted on 2008-10-16
7
543 Views
Last Modified: 2016-02-12
I recently built a stored procedure that uses a cursor to loop on a recordset of attorneys, builds a temporary view containing calendar events for each attorney, and exports that view to an external XML file.  The temporary view and XML export code works, but I had to do some funky stuff with the code to get it to work.  This made me start thinking about SSIS.  

Is an XML export type of thing that SSIS is made for?  Additionally, the sproc described above is just one of several in my project.  Can I call an SSIS procedure from a TSQL sproc?
0
Comment
Question by:jdana
  • 3
  • 2
7 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22731515
You need to use a XML task for source or destination?
You can call a SSIS package from a Stored Procedure directly or from a SQL Job.
Give more feedback!
regards!
0
 

Author Comment

by:jdana
ID: 22731610
If SSIS is appropriate, I'd use it as an XML source.
I'm pumping the XML files to a web server where I front them with PHP.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22731725
You need to read XML source and where you need to insert data? In database?
Regards
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:jdana
ID: 22736081
The data resides IN the database.  The database EXPORTS XML files.  The website READS the XML files.
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 250 total points
ID: 22742689
To call an SSIS Package from T-SQL, use the sp_start_job procedure:
USE msdb;
GO
EXEC dbo.sp_start_job N'SSIS_PACKAGE_NAME';
GO
 
Here is an example of a way to use the .NET Script Componenty with your Stored Procedure and produce an XML destination:
http://www.sqldbatips.com/showarticle.asp?ID=23
 
 
0
 

Author Closing Comment

by:jdana
ID: 31506742
Thanks Hoggzilla,

Perfect solution
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 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