June 29, 2020 at 3:54 pm
Has anyone ever imported the NPI data from the CMS website? I can't seem to get it to work.
https://download.cms.gov/nppes/NPI_Files.html
June 29, 2020 at 6:59 pm
I have built an SSIS package to load the NPI data - the only issue I had was the size. To handle that I compressed the table...
The process is simple...truncate a staging table and load the data from the file to the staging table. Once that is done - start a transaction, truncate the destination and load from stage to destination.
What issues are you having?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2020 at 7:20 pm
It won't load all of the data. Get an error. I'm trying to do a bulk insert.
June 29, 2020 at 7:32 pm
This was removed by the editor as SPAM
June 29, 2020 at 7:57 pm
I've uploaded my code? Did you read it?
June 30, 2020 at 8:52 pm
I don't see any code that was uploaded - it looks like one of your posts was marked as spam.
I have no idea what
It won't load all of the data. Get an error. I'm trying to do a bulk insert.
means. What is the error you are getting?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2020 at 9:10 pm
This reply has been reported for inappropriate content.
I don't see any code that was uploaded - it looks like one of your posts was marked as spam.
I have no idea what
It won't load all of the data. Get an error. I'm trying to do a bulk insert.
means. What is the error you are getting?
It was my post which was marked as spam, presumably because I questioned the lack of detail being fed to us by the OP and he/she didn't like it. I imagine this one will go the same way.
If you check the very first post, you will see a text file attachment which contains some code.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2020 at 9:38 pm
Jeffrey Williams wrote:I don't see any code that was uploaded - it looks like one of your posts was marked as spam.
I have no idea what
It won't load all of the data. Get an error. I'm trying to do a bulk insert.
means. What is the error you are getting?
It was my post which was marked as spam, presumably because I questioned the lack of detail being fed to us by the OP and he/she didn't like it. I imagine this one will go the same way.
If you check the very first post, you will see a text file attachment which contains some code.
I missed that...but it still doesn't answer the question of what error is occurring. My guess is probably filling a drive - either the transaction log or tempdb, but of course that is just a guess. It could just as well be a problem with BULK INSERT itself - and how it is reading the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2020 at 7:27 pm
Sorry that guy had one of my post removed. He is a freelancer and well known on sql central
I'm getting:
July 7, 2020 at 8:52 pm
Sorry that guy had one of my post removed. He is a freelancer and well known on sql central
I'm getting:
<li style="list-style-type: none;">
- data conversion error(truncation)
<li style="list-style-type: none;">
- OLE db provider "Bulk" for linked server "(null)"
<li style="list-style-type: none;">
- Cannot fetch a row from OLE DB provider "Bulk" for linked server "(null)"
The first error indicates an issue with the length of one of the fields - but it looks like you created your table with all varchar(255) columns except for the NPI and REPLACEMENT_NPI. Both of these should be defined as char(10) and not integer since they are a fixed 10 digit code but shouldn't necessarily be an issue.
This leads me to believe the issues is related to FIELDQUOTE - which was introduced in SQL Server 2017. If you are not using 2017 or later then that parameter would not work - and if your database is not in the correct compatibility it could also be a problem.
With that said - I have seen a lot of posts where BULK INSERT just does not handle the field quote appropriately. Not sure what the solution to that is...since I used SSIS to load the file and it just works.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2020 at 10:47 pm
According to the documentation, several of the "fields" in the data represent up to 10 digits of numeric information. While the max number of digits for an INT is, indeed, 10, it's not a full 10 at the most significant place. You should use DECIMAL(10,0) for those, instead.
Several of the columns are date fields but you're not using them as dates and you probably should.
The documentation also clearly lays out the maximum number of characters for each field. I strongly recommend that you get away from the VARCHAR(255) stuff that so many people end up doing because you actually do WANT import failures to occur if they include wonky data that doesn't meet their own spec.
Also, change the VARCHAR columns that have a max length of 2 to CHAR(2). In fact, my general recommendation is that if something is 10 or fewer characters, consider using CHAR instead of VARCHAR.
Also, if things keep blowing up on you for length, perhaps try \ r \ n (without the spaces) for a line terminator.
And, thank you for the awesome link for the NPI data... There are sooooooooo many awesome things I can teach folks with such a readily available data set.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2020 at 10:49 pm
p.s. As Jeffrey Williams pointed out, if you don't have 2017 or better, the FIELDQUOTE option isn't going to work. Post back if that's a part of your problem... we can generate a BCP format file that will take care of that in earlier versions.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2020 at 1:46 pm
OK, THIS IS RIDICULOUS! I NEVER SIGNED UP TO FOLLOW THIS POST, YET I HAVE HUNDREDS OF EMAILS ON IT. GET ME OFF THIS POST PLEASE!
Steve Anderson
July 8, 2020 at 1:51 pm
OK, THIS IS RIDICULOUS! I NEVER SIGNED UP TO FOLLOW THIS POST, YET I HAVE HUNDREDS OF EMAILS ON IT. GET ME OFF THIS POST PLEASE!
Just hit the Unsubscribe button at the top of the page.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply