SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding a Header Row to a SSIS package exporting out as XML


Adding a Header Row to a SSIS package exporting out as XML

Author
Message
WayneHess
WayneHess
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 51
I've been asked to create an SSIS package to export data out as and XML file.
I've got the xml file down but now the customer is wanting a header row added to the first line of the xml file.
I've tried adding it in the Header row for the flat file destination but getting an error that it cannot parse the Header file.
Any suggestions would be appreciated.
Thanks in Advance

Wayne
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45523 Visits: 14925
What do you mean by a header row? By definition XML doesn't have rows, it has elements and attributes.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
WayneHess
WayneHess
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 51
I had created the SSIS package to pull just the student data.
What the vendor was wanting was the XML information <?xml version="1.0" encoding="utf-8"?> added to the first row of the extract.
I finally found the place to add it and how to add it after several searches.
Thanks
inevercheckthis2002
inevercheckthis2002
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 485
Please post your solution!

Thanks...
WayneHess
WayneHess
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 51
In the heading area when setting up the destination file I had to add in <?xml version="1.0" encoding="utf-8"?>
That gave me what the vendor was needing in the file.
inevercheckthis2002
inevercheckthis2002
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 485
If you can possibly describe how you get the file to output, I would greatly appreciate it.

I have a query where I can get the XML that I need, but When I run the query with 'Results to text' or 'Results to file' selected, the text or file contains a line starting with the characters "XML..." and a second line containing dashes '--------------...'

I just need the XML in a file.

Thanks for any direction that you may provide!
WayneHess
WayneHess
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 51
Here is how I setup my SQL Script to pull the data into an XML File.

SELECT(select s.StudentNumber As DisplayID, s.PersonID AS ExternalAccountID, sc.number AS SchoolID,s.FirstName, s.LastName, s.Gender,
CONVERT(varchar,s.birthdate,101) AS Birthdate, s.Grade AS GradeLevel,
CASE WHEN ts.PhysicalDate IS NOT NULL THEN CONVERT(varchar,ts.physicaldate,101)ELSE '' END As LastPhysicalDate,
MAX(CASE WHEN pe.eligibility ='F' THEN '3'WHEN pe.eligibility = 'R' THEN '2'ELSE '1'END) As EconomicIndicator
FROM student s
JOIN SchoolYear sy ON sy.endYear = s.endYear
LEFT JOIN TempScreening ts ON ts.personid = s.personID
JOIN calendar ca ON ca.calendarID = s.calendarID
JOIN School sc ON sc.schoolID = ca.schoolid
LEFT JOIN POSEligibility pe on pe.personid = s.personid
where studentnumber IS NOT NULL and sy.active = 1 and s.endDate IS NULL or s.endDate >=GETDATE()
AND ca.schoolID NOT IN( 12,13,32,16,34,14,15,25)
GROUP BY s.studentNumber, s.personID, sc.number,s.firstName, s.lastName,s.gender, s.birthdate, s.grade,ts.physicaldate

FOR XML PATH('Student'), root('Students')
)as Data

Hope this helps
WayneHess
WayneHess
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 51
Forgot to mention. I'm running mine in an SSIS package so just created a Flat File destination to send the file.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search