July 24, 2015 at 6:36 pm
Hi,
Good Morning All,
I am having a file with the customer information stored , it may be in xml, xls,txt,csv ..etc..
I want to create a SSIS package as follows
Conditions
If there are about 100 records in text file, if there is an error at 43 and at 67 record respectively , it should capture 43 and 67 record in failure folder and remaining 98 records , should be processed
1) Successful record into table and move the success record from the folder
to new path say( Success folder) (98 records to table)
2) Unsuccessful records to new path (Failure folder) (2 lines )
3) Error message to capture the failed records and store them in another folder(Error log) (2 line failure information)
While writing the 3rd condition to error log table , it has to point out the record which is failed for what reason, say it may be due to invalid data type for column 10 for 43 record, and incorrect syntax error at 67 record.
How can we do this, is there any sample to check the same.
Thanks,
July 27, 2015 at 7:37 am
yuvipoy (7/24/2015)
Hi,Good Morning All,
I am having a file with the customer information stored , it may be in xml, xls,txt,csv ..etc..
I want to create a SSIS package as follows
Conditions
If there are about 100 records in text file, if there is an error at 43 and at 67 record respectively , it should capture 43 and 67 record in failure folder and remaining 98 records , should be processed
1) Successful record into table and move the success record from the folder
to new path say( Success folder) (98 records to table)
2) Unsuccessful records to new path (Failure folder) (2 lines )
3) Error message to capture the failed records and store them in another folder(Error log) (2 line failure information)
While writing the 3rd condition to error log table , it has to point out the record which is failed for what reason, say it may be due to invalid data type for column 10 for 43 record, and incorrect syntax error at 67 record.
How can we do this, is there any sample to check the same.
Thanks,
While there are a number of SSIS transformations that can re-direct records to different destinations based on conditions, data records coming in to the transformation still have to be valid from a data type perspective, based on how you define the data type for the portion of your package that reads the data. You can't solve data type problems in the manner you are referring to without using a SCRIPT TASK to read the data and identify things that don't fit the data type within the script. If your data has some kind of "record type" column, you could use such a column to determine the destination, but again, that doesn't solve a data type problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 7:42 pm
Thanks for your replay Steve.
As i said it is one of the condition to check.There are multiple possible ways are there for data not loaded in to database, i have taken datatype as an example.
Is there a webpage or predefined scripts avail with example to check, if so can you please share the same of this kind.
Main aim to capture the error information and store in the database/file to send the information to customer on failed data at where.
Thanks.
July 27, 2015 at 9:12 pm
yuvipoy (7/27/2015)
Thanks for your replay Steve.As i said it is one of the condition to check.There are multiple possible ways are there for data not loaded in to database, i have taken datatype as an example.
Is there a webpage or predefined scripts avail with example to check, if so can you please share the same of this kind.
Main aim to capture the error information and store in the database/file to send the information to customer on failed data at where.
Thanks.
It's not likely such could easily be found, or someone would probably have responded with a link already. That kind of work is usually judged as too much effort, and potentially, just too difficult to support. As the columns will ALL have to have customized code for each and every one, no mere code template could reasonably cover all the bases.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 11:36 pm
Step:1
How can we find the column validation of my Rows, say if there are 25 columns are there how can we validate columns with database tables.
July 28, 2015 at 7:23 am
yuvipoy (7/27/2015)
Step:1How can we find the column validation of my Rows, say if there are 25 columns are there how can we validate columns with database tables.
Trying to import them into a table isn't going to do that. You need custom code to read raw data and make a decision about whether it's the right data type or not. Once you have that code, then you can add any code needed to validate other characteristics of the data that might not be data-type related. That's why this kind of thing is ALWAYS custom work, and a ton of it, because you have to do it for every single field. The only way people generally do this is to treat ALL data as text and import it into a staging table, and then validate the data in the staging table with custom code to validate each individual field. As part of validation, you can create an Exceptions table that will identify the problem staging table records, and only import the good ones from staging. It's still a ton of work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2015 at 2:24 pm
Is there an example / sample with 3 columns
It will help to me to learn on this.
August 7, 2015 at 8:38 am
yuvipoy (8/6/2015)
Is there an example / sample with 3 columnsIt will help to me to learn on this.
Just constructing such an example would require more time than I have available. There's no easy way to be sure you could cover all the possible problems, as data requirements are often ill-specified or vague, and often, users are impossible to pin down on what they're willing to accept. You'd learn a lot more by talking to someone you know who actually writes SSIS packages or at least performs ETL tasks. This isn't a subject you can just throw a paragraph or two at and have it be sufficient. It usually takes years of experience to become good at it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 2:18 pm
sgmunson (8/7/2015)
Just constructing such an example would require more time than I have available. There's no easy way to be sure you could cover all the possible problems, as data requirements are often ill-specified or vague, and often, users are impossible to pin down on what they're willing to accept. You'd learn a lot more by talking to someone you know who actually writes SSIS packages or at least performs ETL tasks. This isn't a subject you can just throw a paragraph or two at and have it be sufficient. It usually takes years of experience to become good at it.
As you said "You'd learn a lot more by talking to someone you know who actually writes SSIS packages ", came here to get some knowledge on this, i am asking for basic information like how can we proceed.
Guidance would help in learning... 🙂 🙂
August 10, 2015 at 3:01 pm
yuvipoy (8/10/2015)
sgmunson (8/7/2015)
Just constructing such an example would require more time than I have available. There's no easy way to be sure you could cover all the possible problems, as data requirements are often ill-specified or vague, and often, users are impossible to pin down on what they're willing to accept. You'd learn a lot more by talking to someone you know who actually writes SSIS packages or at least performs ETL tasks. This isn't a subject you can just throw a paragraph or two at and have it be sufficient. It usually takes years of experience to become good at it.
As you said "You'd learn a lot more by talking to someone you know who actually writes SSIS packages ", came here to get some knowledge on this, i am asking for basic information like how can we proceed.
Guidance would help in learning... 🙂 🙂
I write SSIS packages. I know enough about them to know that you can't just take the basic concepts of ETL and slap them into a couple of paragraphs and think that it will help much. There are too many details that need to be covered. What you originally detailed is common from the point of view of the overall process. Typically, incoming data is written to a "staging" table that has no constraints or defaults and nothing but character fields, and no error checking occurs during the movement of the incoming data into that table. Then, the data is copied to a "clean" table that is identical to the staging table, but also provides a column to mark the record as having an error, and in many cases, an error table exists to record the errors instead. Either way, the error checking process comes next, and records with errors are identified by an entry in the error table, or an indicator in the clean table, or both. Then, a query will look at the error table and/or the clean table and pass good records into production, and bad records somewhere else if needed, which isn't entirely necessary given the existence of the error table. Then you output a report detailing what took place.
Just putting together a working example for all of that is well beyond the scope of volunteer effort. It's not just a few minutes work - getting it tested and working and then scripted could easily take a good week or two of full time effort. Have you done any Google searches for SSIS examples ? My guess is that working samples with scripts and all are going to be rare, because every field has to have it's own validation, and that's where the hard work is. Just creating a good validation for just one date field can be challenging.
How about this... Post back a list of all the things you can think of to check to see if a text value coming in represents a valid date in MM/DD/YYYY format. Take your time and think it through...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 3:15 pm
If you plan to use SSIS for more than just the basic stuff then you must take the time to learn how to use SSIS.
Break down your problem into it's steps and there's probably something already posted that can solve that problem, but you're not likely to find anything that solves all your problems.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply