SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert Error: Linked Server "(null)"


Bulk Insert Error: Linked Server "(null)"

Author
Message
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15082 Visits: 9006
This one is driving me batty. Please read ALL the details before responding. There's a kicker at post-end.

We converted a DTS pkg to SSIS 4 years ago. We ended up with several Bulk Insert Tasks which worked beautifully. Now I need to set up a Bulk Insert Task using the same table (different name) and same file on another database. So we scripted out the original table, SANS foreign keys, ran that code on the new database and created a new package to take that file to the new table.

Bulk Insert Task is exactly set up the same in this package as it is in the other package.

Connection: MyDB
DestinationTable: MyDB.dbo.NewTableName
Format: Specify
RowDelimiter: {CR}{LF}
Column Delimeter: ~
File: MyFileConnMgr

CodePage: OEM
DataFileType: char
BatchSize: 0
LastRow: 0
FirstRow: 0
Options: (nothing checked)
SortedData:
MaxErrors: 0

When it kept erroring out, I even copied the other package, deleted unnecessary schtuff, pointed the Bulk Insert Task to the new DB and table. Still getting the damn error.

SSIS Error

Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (10) was exceeded.Bulk load data conversion error (truncation) for row 11, column 38 (Column38)...<lots of errors along the same line as this>...


Now the file has 3 "date" columns that come across in 10 digit format. YYYYMMDD with no separators. When we try to do a normal Data Flow Task, SSIS complains that it can't convert without a loss of data. So I know these fields are trying to cause problems. I don't know if it's related to the above error though, and I don't want to use DF Task with Transformations.

My confusion is that the Bulk Insert Task used to work. Still does if I use the original database and the original table name. I want to use this methodology on the new package. No fuss, no muss.

So why does it not work when I'm trying to recreate it exactly?

Code and 4 line sample file are attached.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Attachments
MyFile.txt (43 views, 742 bytes)
Create_MyTable_Script.txt (54 views, 2.00 KB)
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15082 Visits: 9006
Riiiggght. Quite a DUH moment for me here.

Bulk Insert Task doesn't work if the table has more or less columns than the file has.

To make it work, I had to delete column 39, then run the task, then re-add column 39, then do my update on column 39. And if I'd looked at more than that one task on the original package, I'd have seen the solution.

I hope this helps someone else out. Quite a learning experience for me, this was. <headthunk>

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
chris.stuart
chris.stuart
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 796
I had the same problem and I think that it is a bug in SSIS.

When you select the table to be populated in the drop down list in the OLE Destination component, you have the "Name of the table or the view:", you get the owner of the table and the table name i.e. dbo.table1.
Manually go to the dtsx package and search for the entry dbo.table1 and add the database name to it as well, i.e
MyDB.dbo.Table1
When you go to the OLE Destination again, it will show the database, owner & table. This should sort out our problem.
PS, you will not be able to select the table again from the drop down box as it will only populate it again with the owner & table name which will fail again.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15082 Visits: 9006
chris.stuart (10/21/2010)
When you select the table to be populated in the drop down list in the OLE Destination component, you have the "Name of the table or the view:", you get the owner of the table and the table name i.e. dbo.table1.


You misunderstand the situation. I'm not using an OLE DB Destination component. I'm not even in Data Flow at all. I'm using the Bulk Insert Task inside the Control Flow screen.

Bulk Insert Task has 4 tabs. General, Connection, Options, Expressions. Under Connection, it has Connection (for connection manager), DestinationTable (drop down list for the tables in the connection), Format, RowDelimiter, ColumnDelimeter, and SourceConnection/File.


chris.stuart (10/21/2010)

Manually go to the dtsx package and search for the entry dbo.table1 and add the database name to it as well...

When you go to the OLE Destination again, it will show the database, owner & table. This should sort out our problem.
PS, you will not be able to select the table again from the drop down box as it will only populate it again with the owner & table name which will fail again.


Not that it matters, but for future reference, I'm not sure I understand this last bit. "Manually go into the dtsx package?" I'm already in BIDS. If that isn't manually in the package, what is? Always remember to give more details on directions like this when answering other people's questions. It's too generic to give them something to google on.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
chris.stuart
chris.stuart
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 796
Ok, it was just a quick reply I must admit.

I only now see that you found you solution already.

But to add, I had a problem with the Bulk insert (cant remember now what it was, might have been the same thing) and decided to use the OLE destination component. The problem I had was also a null error, but it was solved with the database, owner table solution.
When I say manually, I love editing the packages outside of BIDS, and in 2008 it works much better (manual edit of the package) where as in 2005 I experienced some problems with the manaul edit of the package.

On a persoanl note, I think I need a red bull today.
Thx
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15082 Visits: 9006
chris.stuart (10/21/2010)
When I say manually, I love editing the packages outside of BIDS, and in 2008 it works much better (manual edit of the package) where as in 2005 I experienced some problems with the manaul edit of the package.


And how do you do this? I'd like to know because I have no idea how packages can be edited outside of BIDS.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
chris.stuart
chris.stuart
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 796
The package is just a XML file, open it up in any test editor. I use Textpad for this.
For instance the reason I do the manual edit of the file is if you have lot of columns to add in the script transformation component. I would just add the all the columns (sometimes upto 150 of them in some cases) with out renaming them and the in the text editor, you can copy and paste the column names over the name "Column" "Comun 1" etc. In the Textpad editor you can also do "block selects" which makes this copy and paste a 1 step process.
So instead of adding the input and output columns in the script transformation component, renaming each of them to the correct names, changing the types, changing the size of the columns etc, you can add the required number of output columns, then in Textpad do a block select of the names you want to copy and block paste them over the generated names.
If you are carefull you can do the column type and column size as well with the block select in textpad, but i normally would do this in BIDS itself.
The XML used in 2005 and 2008 differs a bit in the formatting of the xml itself, but it still is just xml which you can edit and format as you wish.
BIDS will in anycase reformat the xml once you save the package inside it again.
BUT, as mentioned before, I've had 1 or 2 problems before in 2005, but currently on 2008 R2, and have had no problems with manual edit of the pacakges.
Hope this helps
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15082 Visits: 9006
Thanks, Chris. That does help.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search