July 15, 2013 at 9:17 am
I'm new to SSIS packages so feel free to yell at me if I'm making this more difficult, and please forgive my lack of terminology.
I have an input file with multiple File Headers (I think that's what they're called). Each row is prefixed with a 3 digit identifier to tell you want "type" of record it is. Based on the "type", the remaining rows follow a set of Fixed Width Columns (which differ depending on the "type")
EMP=Employee
STO=Store
PRT=Part
So an example would be:
EMPJohn 04870 2013010112000001
STOMyStore Phoenix, AZ
PRT104840 000123
PRT104840 000123 A98754
PRT104840 000123 B613247321+12348
And that would be repeated for multiple employees.
I need to import this data into multiple database tables. I did some searching on the Forums and I've been following this article[/url], which I believe will get me what I need. So I have a ragged right input file, and a data flow task that gives me the rows. Next I added a Script Transform and a new column that will get me the "type" (and all the other possible columns). So then in the script task I can check against the type, and then use a substring to set the remaining column values.
Is this a good way to approach this or am I not aware of something much simpler? Any advice/point to a resource would be greatly appreciated.
Thanks!
July 17, 2013 at 12:43 pm
Hi,
I think we need to be more clear of the structure of the data the file and the relationships between the lines to answer this for sure. However it looks like the relationship between the Employee, Store and Part is sequential which would mean you would need to read the file line by line which involve scripting for sure.
Do you know the expected columns in the files for EMP,STO and PRT?
Regards
Daniel
July 23, 2013 at 10:27 am
What is the relationship between EMP, STO and PRT.
Can an EMP have more than one STO
Is EMP always followed by one and only one STO and is STO always followed by at least one PRT.
We need to know more about the end data results to advise more, but this format looks horrible.
This really would be better as XML if you have any control over the format.
<FILE>
<EMP>
<ID>...</ID>
<DATE>...</DATE>
<STORES>
<STORE>
<NAME>...</NAME>
<CODE>...</CODE>
<PARTS>
<PRT>...</PRT>
<PRT>...</PRT>
<PRT>...</PRT>
</PARTS>
</STORE>
<STORE>
<NAME>...</NAME>
<CODE>...</CODE>
<PARTS>
<PRT>...</PRT>
<PRT>...</PRT>
<PRT>...</PRT>
</PARTS>
</STORE>
</STORES>
</EMP>
</FILE>
As this way at least the relationships are defined within the file and you can use xQuery to navigate the hierachy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply