August 7, 2014 at 11:56 am
How should I approach on creating SSIS package to load data from multiple files to multiple tables. Also, Files will have data which might overlap so I might have to create stored procedure for it. Ex. 1st day file -data from au.1 - aug 10 and 2nd day file might have data from aug.5 to aug 15. So I might have to look for max and min date and truncate table with in that date range.
August 7, 2014 at 12:11 pm
More information is needed. Are these files the exact same layout/format? How do you determine who 'wins' when data overlaps? Are you allowed to create/affect schema and can you build a staging structure to manipulate rows in?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 7, 2014 at 12:23 pm
These files are in same format and in same folder. Going to create staging table to get date range.
August 7, 2014 at 12:51 pm
If data overlaps between two files, how do you determine who 'wins'? I realize that it's possible there shouldn't be discrepencies, but humor me. What's your merge conflict resolution expectation?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 7, 2014 at 12:53 pm
might have to add extra column when loading data to staging table
August 7, 2014 at 1:01 pm
2sbhagat (8/7/2014)
might have to add extra column when loading data to staging table
Probably more than one, but you're not answering the question in the manner I'm hoping to find out. Same key, two files, which one wins? How do you determine? Ignore the code, what's the business rule of which one is considered the most accurate, in plain speech?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 7, 2014 at 1:08 pm
newer files are accurate but it comes with overlap data.
August 7, 2014 at 1:16 pm
What mechanisms do you have to determine which is the most recent file for overlaps? Is the report/pull date in the text file, do you need to extract it out of the file name, or do you have to look at file creation information on your SAN? If file creation info, are these files FTP'd to you so they can be out of sync or bulk dropped, or do you guys generate them yourselves so you can have some consistency in how it's done?
I'm asking all these questions because you're basically implementing your own form of merge replication for these flat files. While necessary, these are important things to have nailed down before you writw a single line of code, or you can end up having to completely start over because of a single piece.
Side Note: This is a forum, not a simple Q&A site. If you are going to continue being the absolute tersest you can with no extrapolation I'm going to walk away. I am not going to spoon feed you end to end through this, feeling like I'm dragging every inch of information out of you. Get off the blackberry and find a PC.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply