March 10, 2017 at 12:01 pm
I am looking for some tsql code to help me on a nightly import job. I am using stor procs to move data from a linked server MySQL Database into a SQL database on prem. I want to be able to reject the data type issues or invalid information that may arise into a reject table so I might look at it later or in the morning. It's like every night it's a 911 to get my computer on and before reports go out. It would be helpful to reject the one or two lines of data and let everything else import in so I may look at it later.
Thanks!
MCSE SQL Server 2012\2014\2016
March 10, 2017 at 12:29 pm
lkennedy76 - Friday, March 10, 2017 12:01 PMI am looking for some tsql code to help me on a nightly import job. I am using stor procs to move data from a linked server MySQL Database into a SQL database on prem. I want to be able to reject the data type issues or invalid information that may arise into a reject table so I might look at it later or in the morning. It's like every night it's a 911 to get my computer on and before reports go out. It would be helpful to reject the one or two lines of data and let everything else import in so I may look at it later.Thanks!
Wild suggestions:
1. Export the data from the MySQL DB into a flat file. Then import the flat file into SQL Server with BULK INSERT. This allows to send all the invalid rows into an error file.
2. Use SSIS to transfer the data from one server to another.
In short, drop the linked server and use a tool designed for ETL.
March 10, 2017 at 12:35 pm
Luis Cazares - Friday, March 10, 2017 12:29 PMlkennedy76 - Friday, March 10, 2017 12:01 PMI am looking for some tsql code to help me on a nightly import job. I am using stor procs to move data from a linked server MySQL Database into a SQL database on prem. I want to be able to reject the data type issues or invalid information that may arise into a reject table so I might look at it later or in the morning. It's like every night it's a 911 to get my computer on and before reports go out. It would be helpful to reject the one or two lines of data and let everything else import in so I may look at it later.Thanks!
Wild suggestions:
1. Export the data from the MySQL DB into a flat file. Then import the flat file into SQL Server with BULK INSERT. This allows to send all the invalid rows into an error file.
2. Use SSIS to transfer the data from one server to another.In short, drop the linked server and use a tool designed for ETL.
Had I designed this that's what I would done however this was inherited and I am tired of watch it at night, I did post the same question in SSIS and they suggested I post in tsql, I should have mentioned that first I suppose but it's Friday and I am drained.
MCSE SQL Server 2012\2014\2016
March 10, 2017 at 1:30 pm
One option is to import the data into a staging table without whatever constraints are causing you issues, then from there load the valid data into your final table and the bad data into an audit table. That would require you to identify the causes of your errors and figure out how to trap for them.
March 10, 2017 at 2:54 pm
I like what ZZartin is suggesting but perhaps you could dial it back a bit.
Make the staging table without constraints or at least to match the source data. If the source data changes that's another story.
Add a field to identify whether or not the data imports.
Perform your T-SQL and update the new field with a Y/N value.
It's ugly, it's not very helpful in figuring out why something didn't import, but it will give you an idea of what's going on and how to resolve it.
I'm guessing you're going to end up with a LOT of case statements during the import as you find exceptions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply