March 7, 2017 at 12:20 pm
I have a nightly import job that imports from a MySQL linked server into my SQL database. Sometimes data comes over that is incorrect and causes the entire job to fail. example would be Alpha Numeric trying to insert into an INT column, CV154BB should be 2013. I want to create a temp table that will hold items that will not insert into the production tables. I know I can create a temp table with the same columns with different data types but basically I am looking for a reject table that I can look at in the morning, fix, and then insert into a production table. How would I let SSIS know to put data that cannot insert into a different temp table? I am in a DW environment so inserting data later would not have any major impacts. Any ideas would be great, this is my first thought in trying to keep the data moving and stop the all or nothing for data inserts.
Thanks!
UPDATE: I can use a perm table, it does not have to be a temp table, I really just need to get the data into a table so I can correct it in the morning and not stop the entire import.
MCSE SQL Server 2012\2014\2016
March 7, 2017 at 12:46 pm
lkennedy76 - Tuesday, March 7, 2017 12:20 PMI have a nightly import job that imports from a MySQL linked server into my SQL database. Sometimes data comes over that is incorrect and causes the entire job to fail. example would be Alpha Numeric trying to insert into an INT column, CV154BB should be 2013. I want to create a temp table that will hold items that will not insert into the production tables. I know I can create a temp table with the same columns with different data types but basically I am looking for a reject table that I can look at in the morning, fix, and then insert into a production table. How would I let SSIS know to put data that cannot insert into a different temp table? I am in a DW environment so inserting data later would not have any major impacts. Any ideas would be great, this is my first thought in trying to keep the data moving and stop the all or nothing for data inserts.
Thanks!
A persisted physical table is what you need here, surely? Temp tables have limited scope and are likely to be 'garbage collected' before you even see the data they contain. Not only that, they are rather difficult to work with in SSIS, given that SSIS expects objects to exist at design time.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 7, 2017 at 1:13 pm
either one will work temp or perm, I just want to be able to get the data in and deal with the one line that would not go in later. A reject table would be nice, I am importing several tables. my thought is I can drop the temp table once I am done with the one item that would not insert, I am open for ideas...
I am using SP's to insert the data for the SSIS to import, executing SQL tasks in steps so I am able to identify any issues. It's better than going through 1000's of lines of code trying to find the issue on the one line item and now I am looking to keep the data inserting and kick out the bad data to a reject table to be fixed later. I am tired of watching this every night no matter where I am.
MCSE SQL Server 2012\2014\2016
March 7, 2017 at 1:42 pm
lkennedy76 - Tuesday, March 7, 2017 1:13 PMeither one will work temp or perm, I just want to be able to get the data in and deal with the one line that would not go in later. A reject table would be nice, I am importing several tables. my thought is I can drop the temp table once I am done with the one item that would not insert, I am open for ideas...I am using SP's to insert the data for the SSIS to import, executing SQL tasks in steps so I am able to identify any issues. It's better than going through 1000's of lines of code trying to find the issue on the one line item and now I am looking to keep the data inserting and kick out the bad data to a reject table to be fixed later. I am tired of watching this every night no matter where I am.
I'd go with Phil's suggestion. It's the same thing that some ERP systems use for importing data. What if fixing the data can wait until tomorrow? What if the server crashes when you are working on the problem rows? And at some point, you could possibly dump that clean up off to someone else.
I think there are more advantages to have a persisted table.
Sue
March 7, 2017 at 1:50 pm
okay, let's use a perm table, how do I get the data that will not insert to go to another table for later correction?
MCSE SQL Server 2012\2014\2016
March 7, 2017 at 4:28 pm
lkennedy76 - Tuesday, March 7, 2017 1:50 PMokay, let's use a perm table, how do I get the data that will not insert to go to another table for later correction?
This should give you the gist of it:
Configure an Error Output in a Data Flow Component
I think it's Redirect row option on the Errors Output and then map that to a new destination for the table for the rejects. So you'd have a success flow and a failure flow.
Sue
March 8, 2017 at 10:01 am
as this is a good solution;
https://msdn.microsoft.com/en-us/library/ms140083.aspx
all my components are in the Control Flow. Once I find an answer hopefully I will put it up here.
MCSE SQL Server 2012\2014\2016
March 8, 2017 at 10:15 am
lkennedy76 - Wednesday, March 8, 2017 10:01 AMas this is a good solution;
https://msdn.microsoft.com/en-us/library/ms140083.aspxall my components are in the Control Flow. Once I find an answer hopefully I will put it up here.
You don't use data flows? Why not?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 8, 2017 at 10:59 am
I am putting several SP's in a sequence container with begin and commit transaction and failure emails.
MCSE SQL Server 2012\2014\2016
March 8, 2017 at 11:22 am
lkennedy76 - Wednesday, March 8, 2017 10:59 AMI am putting several SP's in a sequence container with begin and commit transaction and failure emails.
That's not a reason; it's an alternative solution architecture.
Working in this way does not allow you to redirect 'bad' data rows ... you'll have to work out a way of doing that in T-SQL. SSIS features cannot help you if you put all your program logic in stored procs.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 8, 2017 at 12:12 pm
I use data flows just not in this instance, it was created before I arrived Phil. We are importing from a linked server, thus SP's with temp to prod tables. I guess I can ask this question the in t-sql forums.
MCSE SQL Server 2012\2014\2016
March 8, 2017 at 12:18 pm
lkennedy76 - Wednesday, March 8, 2017 12:12 PMI use data flows just not in this instance, it was created before I arrived Phil. We are importing from a linked server, thus SP's with temp to prod tables. I guess I can ask this question the in t-sql forums.
OK, understood. Good luck!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply