October 18, 2010 at 12:24 pm
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.
October 20, 2010 at 7:10 am
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>
October 21, 2010 at 3:36 am
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.
October 21, 2010 at 4:19 am
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.
October 21, 2010 at 4:37 am
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
October 21, 2010 at 4:45 am
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.
October 21, 2010 at 5:31 am
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
October 21, 2010 at 5:54 am
Thanks, Chris. That does help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply