September 4, 2012 at 5:55 pm
A PACKAGE TO LOOP OVER A SET OF EXCEL AND FLAT FILES and EXTRACT the INFORMATION ONTO SQL SERVER DESTINATION WITH THE TABLE INFORMATION NOT EXCEEDING 10 MB SIZE.
CONDITIONS:
IF PACKAGE FAILS, WE NEED TO GET AN EMAIL REPORTING FAILURE.
IF DATA GATHERING TASK FAILS ONCE, ATLEAST ONE RETRY SHOULD BE PERFORMED AUTOMATICALLY.
could someone plz help me
September 4, 2012 at 6:04 pm
This is a deep set of requirements, I'm not sure if you're aware of how deep this rabbit hole is going to go. Also, please less caps next time.
SS53217 (9/4/2012)
A PACKAGE TO LOOP OVER A SET OF EXCEL AND FLAT FILES
First, this will require two loops, minimum, as the excel and flat files will require different data source connections. If they're in different formats it's going to be even worse. Data Source Metadata can't mutate between files, so you'll need a different data source for each file format, including simply having a diff # of columns between the flat files.
EXTRACT the INFORMATION ONTO SQL SERVER DESTINATION WITH THE TABLE INFORMATION NOT EXCEEDING 10 MB SIZE.
What the? *blinks* You're wanting to restrict the amount of data you include into the database by... data size? 'eh? Please explain the rational behind this restriction. It's unwieldy, difficult when you start involving multiple tables in the target db, and if you have default values, LOB information, or other problematic information this could be a small article in simple talk by itself.
IF PACKAGE FAILS, WE NEED TO GET AN EMAIL REPORTING FAILURE.
This you'll want to do from the SQL Agent. It can be set to email on failures. Don't fight with this internal to the package.
IF DATA GATHERING TASK FAILS ONCE, ATLEAST ONE RETRY SHOULD BE PERFORMED AUTOMATICALLY.
In SSIS when looking at the loop control, you'll notice under the Execution node in properties there's a series of Error count values and fail package items. What you want is MaximumErrorCount. Set this to 2. Be forewarned that messing around with those too much without building your own error control set will seriously threaten the package's integrity. On a single control for a single retry, you should be alright with some forethought.
Make sure you archive OUTSIDE of the loop, as you don't want to reprocess the same data because the archive step failed. Usually it's safer to set the retry on the dataflow control itself, but even then a partial
load without using a staging table flush/rebuild can cause duplicated data. As I mentioned, beware retries without intervention and confirmation, there's a lot of little things that can go wrong with them, but they can be contained if you think about all the little ways duplicated data can be dealt with.
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
September 7, 2012 at 2:49 am
For the retry requirement, you can also configure retry attempts in the SQL Server Agent job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply