Hi
I am trying to build a query that will give me hierarchical data but am
having little success.
How can I change the query below to give me xml data in a hierchical format,
nesting multiple customer addresses under one customer id?
Thanks for your asistance
Regards
Habib
---
use adventureworks
select c.CustomerID, co.FirstName, co.LastName,
a.AddressID, pa.AddressLine1, pa.City, pa.PostalCode
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
join Sales.CustomerAddress a
on c.CustomerID = a.CustomerID
join Person.Address pa
on a.AddressID = pa.AddressID
where c.CustomerID in( 254, 11532)
for xml pathUse a nested query
select c.CustomerID,
(select co.FirstName,
co.LastName,
a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Person.Contact co
inner join Sales.CustomerAddress a on c.CustomerID =
a.CustomerID
inner join Person.Address pa on a.AddressID = pa.AddressID
where c.CustomerID = co.ContactID
for xml path,type)
from Sales.Customer c
where c.CustomerID in( 254, 11532)
for xml path|||Mark,
That was very helpful. Thanks.
The key was to use the Type directive in the nested query. I missed that
when reading BOL.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||Again thanks for your help.
I have a nice little query as below:
How can I wrap this with two additional tags
<request>
<addOrUpdate>
<Customers>
..
</Customers>
</addOrUpdate>
</request>
--
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers')
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21
---
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||This should work
select (
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers') ,root('addOrUpdate'),type)
for xml path ('request')|||
Mark,
I discovered something similar when I was tinkering with the query.
select ( select ...
for XML PATH ('Customer'),type) as [addOrUpdate]
for xml path ('request'),type
But try as i might, I cant seem to get end tag to appear after each
</Customers>
I expect the difference is that the record is committed after each
</addOrUpdate>, so without it wrapping each record, the entire XML file is
either committed or entirely rolled back.
One might argue the relative merits of either approach but the specification
requires the end tags after each record.
How can I get that in?
Thanks for the assistance so far. It has been very helpful.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148715862.851001.69320@.j33g2000cwa.googlegroups.com...
> This should work
> select (
> select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('Customers') ,root('addOrUpdate'),type)
> for xml path ('request')
>|||Is this what you're after?
select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('addOrUpdate') ,root('request'),type|||Mark,
No, I should have been clearer with what I meant
I have pasted what I want below.
I have tried tinkering with the SQL statement but it seems impossible to me.
Thanks again and regards
Habib
<request>
<addOrUpdate>
<Customers>
<CustomerID>254</CustomerID>
<FirstName>Helen</FirstName>
<LastName>Dennis</LastName>
<address>
<AddressID>185</AddressID>
<AddressLine1>2681 Eagle Peak</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
<address>
<AddressID>861</AddressID>
<AddressLine1>25915 140th Ave Ne</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<request>
<addOrUpdate>
<Customers>
<CustomerID>11532</CustomerID>
<FirstName>Madison</FirstName>
<LastName>White</LastName>
<address>
<AddressID>201</AddressID>
<AddressLine1>6202 Seeno St.</AddressLine1>
<City>Sammamish</City>
<PostalCode>98074</PostalCode>
</address>
<address>
<AddressID>20692</AddressID>
<AddressLine1>6437 Brookview Dr.</AddressLine1>
<City>Redmond</City>
<PostalCode>98052</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<markc600@.hotmail.com> wrote in message
news:1148762909.570472.310590@.38g2000cwa.googlegroups.com...
> Is this what you're after?
>
> select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('addOrUpdate') ,root('request'),type
>|||I believe this will work
select
(select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
for xml path ('addOrUpdate'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('request')|||YeeHaw! That worked.
Thank you very much for your assistance. You are a genius.
I tried various other combinations such as adOrUpdate/Request and
@.addorUpdate but did not know think of using the FOR XML PATH before the
final from clause. Of course now it makes sense.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148799893.889247.42490@.38g2000cwa.googlegroups.com...
>I believe this will work
> select
> (select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> for xml path ('addOrUpdate'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('request')
>
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Friday, March 9, 2012
Hierarchy Data in XML
Hello All
I have two Table Employee and Dept
Employee Columns
EmpID
EmpName
MgrID
DeptID
EmpID(PK) MgrID (FK)
Dept COlumns
DeptID
DeptName
I need to return XMl Hierarchy when I pass DeptID as a parameter
So my final result shoudl look like this
<Dept>
<Emp ID="" Name="" ....>
<Emp ID="" Name="">
</Emp>
</Emp>
<Emp ID="" Name="">
</Emp>
<Emp ID="" Name="">
</Emp>
</Dept>
How do I write this Query
Regards
Hello Sekhar,
Take a look at the example I posted yesterday in microsoft.public.sqlserver.programming
in the post about returning arrays.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
I have two Table Employee and Dept
Employee Columns
EmpID
EmpName
MgrID
DeptID
EmpID(PK) MgrID (FK)
Dept COlumns
DeptID
DeptName
I need to return XMl Hierarchy when I pass DeptID as a parameter
So my final result shoudl look like this
<Dept>
<Emp ID="" Name="" ....>
<Emp ID="" Name="">
</Emp>
</Emp>
<Emp ID="" Name="">
</Emp>
<Emp ID="" Name="">
</Emp>
</Dept>
How do I write this Query
Regards
Hello Sekhar,
Take a look at the example I posted yesterday in microsoft.public.sqlserver.programming
in the post about returning arrays.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
Wednesday, March 7, 2012
Hierarchical XML import?
Hello,
Can anyone point me at a tutorial or sample that shows how to use IS for importing an xml file containing hierarchically arranged records ?
I have a file which contains multiple orders , the orders contain multiple line items.. the file also contains an element with details of the file source etc...
So, I want to make an insert in the FileLog table an then make inserts into the orders table .. then make inserts into the OrderItems table which will have the foreign key from the orders table in the records...
if you get what I mean...
But I have searched hign and low and can't see any info on how to load anything but a very flat xml file structure...
Thanks
Vida.
The XML Source will take a hierarchical XML feed and crack it, giving multiple outputs, one for each level. Have a play.
To handle the FKs you need to enforce order of inserts which cannot be done in a single Data Flow task, so stage the data and use several tasks, one for each successive table with the FK. Raw Files would be the ideal staging area for this, fast and efficient.
|||Darren,
Am I right in thinkin that SQLIS is just not designed to load anything but a flat file containing a simple set of same-format rows?
I just can't seem to find very much info on loading master-detail records , whether in XML or in any ordinary flat file ....
Now I've been given a task of loading a simple text file... first row is a bit of header info.. then the next ten rows are data rows in a simple csv format... last row is a checksum.
20060518,SUPPLIER1,,
EUR,AUD,1.6869,
GBP,AUD,2.4896,
USD,AUD,0.7568,
DKK,AUD,4.4581,
SEK,AUD,5.6246,
CHF,AUD,0.9246,
JPY,AUD,84.5870,
NZD,AUD,1.2293,
NOK,AUD,4.6809,
SGD,AUD,1.2060,
HKD,AUD,5.9217,
AUD,AUD,1.0000,
CAD,AUD,0.8515,
END,2454534,,
And I've got to insert a row in a Header table.. containing the header info.. the data rows have to be individual record inserts into a Data table... and they need to be foreign keyed with the ID of the record in the Header table...
I am looking at the example in the SQLIS website http://www.sqlis.com/default.aspx?54 which shows how to handle different row types... problem there is that my file does not have a row-type field. Its just: first row is header.. the rest are detail.. and the last is checksum....
So I'm just trying to figure out a strategy to do this... what approach should I take ?
BTW: I'm a newbie at SSIS... am reading the Wrox book... but its slow going...
Thanks
|||Multi-format rows can be handled in the same data-flow.When you import them you'll have to interpret the file as simply a file with one long text column in it and then, after you've split the data (see below) parse the proper columns out.
Regarding splitting the data into header and detail records...
If there is a characteristic of each row that determines what type it is then the Conditional Split transform will split it out into the different sections. From the snippet you have posted above it looks like that shouldn't be too difficult (i.e. Are the first 8 characters all digits? If so, its a header record.)
-Jamie
|||Thanks for your advice Jamie..
I suppose I could use the fact that the first eight chars all digits... I was hoping to just be able to say.. this is the First Row in the file.. so do this...
I dunno.. think I'm having some trouble getting my head around the SSIS way of thinking... being a bit too procedural in my approach...
Like.. I have in my head a procedural way that i think this should be done.. but can't quite map it to an SSIS package...
Maybe you could look at that with me?
For instance I feel that the steps I'd need to carry out to load this file are:
1. Read first line and create a FileLoaded rec in the FileLoaded table. Get an ID for this FileLoaded rec. If there is an error inserting the FileLoaded rec then report error (via email and insert of Error rec to LoadErrors table) and exit.
2. for next bunch of lines: insert FileLine rec in File Line table. Use FileLoaded ID as foreign key. If there's any error then report error.. rollback all inserts.. also rollback insert of FileLoaded rec... report error (detailing line of file that problem was encountered at)
3. When END line is reached, commit transaction. Report success (via email and via insert of record in some Logging table).
4. Copy file to archive.
I'm kinda stumped as to how to do this, especially how to FIRST insert a parent (FileLoaded) record BEFORE starting to insert child (FileLine) records. The sample on SQLIS just splits into master-rows and detail-rows.. seems to happen simultaneously.. so would we not have a race condition where child recs with the foreign key could get inserted before the parent recs ... and so cause a ref integrity error?
Also can't see how to start and roll back transactions and exit straight out of a package when some error occurs...
Please bear in mind that I am still learning this SSIS stuff, I have the Wrox book and am working through it.. but, as is always the case, am under pressure to get something done... something which the book just doesn't seem to cover... ie parent-child related data input...
I do really appreciate any time you (or anyone) spends reading my posts and helping...
PJ
|||PJFINTRAX wrote:
I suppose I could use the fact that the first eight chars all digits... I was hoping to just be able to say.. this is the First Row in the file.. so do this...
You can do this in a script component if you really want to, I'm not sure its necassary though. This should help: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx
I dunno.. think I'm having some trouble getting my head around the SSIS way of thinking... being a bit too procedural in my approach...
Like.. I have in my head a procedural way that i think this should be done.. but can't quite map it to an SSIS package...
Maybe you could look at that with me?
For instance I feel that the steps I'd need to carry out to load this file are:
1. Read first line and create a FileLoaded rec in the FileLoaded table. Get an ID for this FileLoaded rec.
Fairly easy to do as I think we've already covered. Once you insert the record you can get the ID that you just created using an Execute SQL Task.
If there is an error inserting the FileLoaded rec then report error (via email and insert of Error rec to LoadErrors table)
SendMail Task will do the emailing.
OnError precedence constraint or the OnError eventhandler can do the error reporting. Personally I would use the OnError eventhandler because you get more information about the error.
and exit.
OnSuccess/OnError precedence constraints handles this
2. for next bunch of lines: insert FileLine rec in File Line table. Use FileLoaded ID as foreign key.
FileLoadedID is, I presume, the record you created in the previous step? As stated, you can get hold of this value using an Execute SQL Task.
If there's any error then report error
Already covered I think...
.. rollback all inserts.. also rollback insert of FileLoaded rec...
SSIS supports MSDTC transactions: http://blogs.conchango.com/jamiethomson/archive/2004/12/14/456.aspx
report error (detailing line of file that problem was encountered at)
That will be a bit harder to given that SSIS doesn't natively support row numbering within the pipeline (it would be quite nice if it did). You can create your own numbering as explained here: http://www.sqlis.com/default.aspx?37
3. When END line is reached, commit transaction.
Transactions again
Report success (via email
SendMail Task
and via insert of record in some Logging table).
Lots of ways of doing this. I would use the OnPostExecute eventhandler!
4. Copy file to archive.
FileSystem task will do this for you.
I'm kinda stumped as to how to do this, especially how to FIRST insert a parent (FileLoaded) record BEFORE starting to insert child (FileLine) records.
I'm currently working on a blog post about this very thing so look out for that in teh next few days (http://blogs.conchango.com/jamiethomson). Its fairly easy, basically you need 2 data-flows. Insert the FileLoaded record in the first data-flow. Pass the child records to the data-flow using a raw file....and then insert them. Easy-peasy!
The sample on SQLIS just splits into master-rows and detail-rows.. seems to happen simultaneously.. so would we not have a race condition where child recs with the foreign key could get inserted before the parent recs ... and so cause a ref integrity error?
Correct. See my previous comment. Raw files are a big help here. I use raw files all over the place in my implementations - they're incredibly useful.
Also can't see how to start and roll back transactions and exit straight out of a package when some error occurs...
Please bear in mind that I am still learning this SSIS stuff, I have the Wrox book and am working through it.. but, as is always the case, am under pressure to get something done... something which the book just doesn't seem to cover... ie parent-child related data input...
I do really appreciate any time you (or anyone) spends reading my posts and helping...
PJ
Hope this has helped!!!
-Jamie
|||Jamie,
thank you very very much for taking the time to go through my post.
I'll go and read all of those references you have given me and hopefully will be back to report some progress!
Thanks again,
PJ
|||Jamie,
I'm kinda stuck for time on this so have been trying to do a few bits of it... not sure how to use a raw file to pause one dataflow until the other has completed....if you could give a very short description to elighten me a bit further?
But anyway.. I think I have the bit about picking out the first record cracked....
I just pumped the file into a script task that added line numbers to the lines of the file... and then did a conditional split based on the line number... if the line num is 1 then I have one output (for parent rec) and if its not = 1 then I have another ouput (for child records)...
Wish I could paste a picture of the dataflow in here...sigh...
Anyway, the script in the script block transform that adds line numbers is really simple:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static LineCounter As Integer = 0
LineCounter += 1
Me.NumberedRowsBuffer.AddRow()
Me.NumberedRowsBuffer.LineNum = LineCounter
Me.NumberedRowsBuffer.LineData = Row.Column0
End Sub
End Class
So the input file gets pumped through this scirpt.. two fields come out, one is the whole line as input from the file, and the other is a line number...
This then goes into the conditional split...
when the conditional split sends out a parent rec I wanted to insert a rec in the parent table and get back a parent ID....
But I've come up stumped against using an OLE DB Command to insert the parent record in the parent table .. I can't figure out how to get return values out of a stored proc called via an OLE DB Command....
Is this something I'm supposed to be able to do?
Thnks in advance,
PJ
Hierarchical XML import?
Hello,
Can anyone point me at a tutorial or sample that shows how to use IS for importing an xml file containing hierarchically arranged records ?
I have a file which contains multiple orders , the orders contain multiple line items.. the file also contains an element with details of the file source etc...
So, I want to make an insert in the FileLog table an then make inserts into the orders table .. then make inserts into the OrderItems table which will have the foreign key from the orders table in the records...
if you get what I mean...
But I have searched hign and low and can't see any info on how to load anything but a very flat xml file structure...
Thanks
Vida.
The XML Source will take a hierarchical XML feed and crack it, giving multiple outputs, one for each level. Have a play.
To handle the FKs you need to enforce order of inserts which cannot be done in a single Data Flow task, so stage the data and use several tasks, one for each successive table with the FK. Raw Files would be the ideal staging area for this, fast and efficient.
|||Darren,
Am I right in thinkin that SQLIS is just not designed to load anything but a flat file containing a simple set of same-format rows?
I just can't seem to find very much info on loading master-detail records , whether in XML or in any ordinary flat file ....
Now I've been given a task of loading a simple text file... first row is a bit of header info.. then the next ten rows are data rows in a simple csv format... last row is a checksum.
20060518,SUPPLIER1,,
EUR,AUD,1.6869,
GBP,AUD,2.4896,
USD,AUD,0.7568,
DKK,AUD,4.4581,
SEK,AUD,5.6246,
CHF,AUD,0.9246,
JPY,AUD,84.5870,
NZD,AUD,1.2293,
NOK,AUD,4.6809,
SGD,AUD,1.2060,
HKD,AUD,5.9217,
AUD,AUD,1.0000,
CAD,AUD,0.8515,
END,2454534,,
And I've got to insert a row in a Header table.. containing the header info.. the data rows have to be individual record inserts into a Data table... and they need to be foreign keyed with the ID of the record in the Header table...
I am looking at the example in the SQLIS website http://www.sqlis.com/default.aspx?54 which shows how to handle different row types... problem there is that my file does not have a row-type field. Its just: first row is header.. the rest are detail.. and the last is checksum....
So I'm just trying to figure out a strategy to do this... what approach should I take ?
BTW: I'm a newbie at SSIS... am reading the Wrox book... but its slow going...
Thanks
|||Multi-format rows can be handled in the same data-flow.When you import them you'll have to interpret the file as simply a file with one long text column in it and then, after you've split the data (see below) parse the proper columns out.
Regarding splitting the data into header and detail records...
If there is a characteristic of each row that determines what type it is then the Conditional Split transform will split it out into the different sections. From the snippet you have posted above it looks like that shouldn't be too difficult (i.e. Are the first 8 characters all digits? If so, its a header record.)
-Jamie
|||Thanks for your advice Jamie..
I suppose I could use the fact that the first eight chars all digits... I was hoping to just be able to say.. this is the First Row in the file.. so do this...
I dunno.. think I'm having some trouble getting my head around the SSIS way of thinking... being a bit too procedural in my approach...
Like.. I have in my head a procedural way that i think this should be done.. but can't quite map it to an SSIS package...
Maybe you could look at that with me?
For instance I feel that the steps I'd need to carry out to load this file are:
1. Read first line and create a FileLoaded rec in the FileLoaded table. Get an ID for this FileLoaded rec. If there is an error inserting the FileLoaded rec then report error (via email and insert of Error rec to LoadErrors table) and exit.
2. for next bunch of lines: insert FileLine rec in File Line table. Use FileLoaded ID as foreign key. If there's any error then report error.. rollback all inserts.. also rollback insert of FileLoaded rec... report error (detailing line of file that problem was encountered at)
3. When END line is reached, commit transaction. Report success (via email and via insert of record in some Logging table).
4. Copy file to archive.
I'm kinda stumped as to how to do this, especially how to FIRST insert a parent (FileLoaded) record BEFORE starting to insert child (FileLine) records. The sample on SQLIS just splits into master-rows and detail-rows.. seems to happen simultaneously.. so would we not have a race condition where child recs with the foreign key could get inserted before the parent recs ... and so cause a ref integrity error?
Also can't see how to start and roll back transactions and exit straight out of a package when some error occurs...
Please bear in mind that I am still learning this SSIS stuff, I have the Wrox book and am working through it.. but, as is always the case, am under pressure to get something done... something which the book just doesn't seem to cover... ie parent-child related data input...
I do really appreciate any time you (or anyone) spends reading my posts and helping...
PJ
|||I suppose I could use the fact that the first eight chars all digits... I was hoping to just be able to say.. this is the First Row in the file.. so do this...
You can do this in a script component if you really want to, I'm not sure its necassary though. This should help: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx
I dunno.. think I'm having some trouble getting my head around the SSIS way of thinking... being a bit too procedural in my approach...
Like.. I have in my head a procedural way that i think this should be done.. but can't quite map it to an SSIS package...
Maybe you could look at that with me?
For instance I feel that the steps I'd need to carry out to load this file are:
1. Read first line and create a FileLoaded rec in the FileLoaded table. Get an ID for this FileLoaded rec.
Fairly easy to do as I think we've already covered. Once you insert the record you can get the ID that you just created using an Execute SQL Task.
If there is an error inserting the FileLoaded rec then report error (via email and insert of Error rec to LoadErrors table)
SendMail Task will do the emailing.
OnError precedence constraint or the OnError eventhandler can do the error reporting. Personally I would use the OnError eventhandler because you get more information about the error.
and exit.
OnSuccess/OnError precedence constraints handles this
2. for next bunch of lines: insert FileLine rec in File Line table. Use FileLoaded ID as foreign key.
FileLoadedID is, I presume, the record you created in the previous step? As stated, you can get hold of this value using an Execute SQL Task.
If there's any error then report error
Already covered I think...
.. rollback all inserts.. also rollback insert of FileLoaded rec...
SSIS supports MSDTC transactions: http://blogs.conchango.com/jamiethomson/archive/2004/12/14/456.aspx
report error (detailing line of file that problem was encountered at)
That will be a bit harder to given that SSIS doesn't natively support row numbering within the pipeline (it would be quite nice if it did). You can create your own numbering as explained here: http://www.sqlis.com/default.aspx?37
3. When END line is reached, commit transaction.
Transactions again
Report success (via email
SendMail Task
and via insert of record in some Logging table).
Lots of ways of doing this. I would use the OnPostExecute eventhandler!
4. Copy file to archive.
FileSystem task will do this for you.
I'm kinda stumped as to how to do this, especially how to FIRST insert a parent (FileLoaded) record BEFORE starting to insert child (FileLine) records.
I'm currently working on a blog post about this very thing so look out for that in teh next few days (http://blogs.conchango.com/jamiethomson). Its fairly easy, basically you need 2 data-flows. Insert the FileLoaded record in the first data-flow. Pass the child records to the data-flow using a raw file....and then insert them. Easy-peasy!
The sample on SQLIS just splits into master-rows and detail-rows.. seems to happen simultaneously.. so would we not have a race condition where child recs with the foreign key could get inserted before the parent recs ... and so cause a ref integrity error?
Correct. See my previous comment. Raw files are a big help here. I use raw files all over the place in my implementations - they're incredibly useful.
Also can't see how to start and roll back transactions and exit straight out of a package when some error occurs...
Please bear in mind that I am still learning this SSIS stuff, I have the Wrox book and am working through it.. but, as is always the case, am under pressure to get something done... something which the book just doesn't seem to cover... ie parent-child related data input...
I do really appreciate any time you (or anyone) spends reading my posts and helping...
PJ
Hope this has helped!!!
-Jamie
|||Jamie,
thank you very very much for taking the time to go through my post.
I'll go and read all of those references you have given me and hopefully will be back to report some progress!
Thanks again,
PJ
|||Jamie,
I'm kinda stuck for time on this so have been trying to do a few bits of it... not sure how to use a raw file to pause one dataflow until the other has completed....if you could give a very short description to elighten me a bit further?
But anyway.. I think I have the bit about picking out the first record cracked....
I just pumped the file into a script task that added line numbers to the lines of the file... and then did a conditional split based on the line number... if the line num is 1 then I have one output (for parent rec) and if its not = 1 then I have another ouput (for child records)...
Wish I could paste a picture of the dataflow in here...sigh...
Anyway, the script in the script block transform that adds line numbers is really simple:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static LineCounter As Integer = 0
LineCounter += 1
Me.NumberedRowsBuffer.AddRow()
Me.NumberedRowsBuffer.LineNum = LineCounter
Me.NumberedRowsBuffer.LineData = Row.Column0
End Sub
End Class
So the input file gets pumped through this scirpt.. two fields come out, one is the whole line as input from the file, and the other is a line number...
This then goes into the conditional split...
when the conditional split sends out a parent rec I wanted to insert a rec in the parent table and get back a parent ID....
But I've come up stumped against using an OLE DB Command to insert the parent record in the parent table .. I can't figure out how to get return values out of a stored proc called via an OLE DB Command....
Is this something I'm supposed to be able to do?
Thnks in advance,
PJ
hierarchical xml by DataSet()->WriteXml()
I'm playing with web-based front-end for hierarchical db in mssql2K.
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
gok
You should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
gok
You should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok
hierarchical xml by DataSet()->WriteXml()
I'm playing with web-based front-end for hierarchical db in mssql2K.
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
--
gokYou should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
--
gokYou should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok
Subscribe to:
Posts (Atom)