| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2011-11-15 : 12:49:26
|
| I have an SSIS package that runs fine in 2005 SSIS. Runs fine as an SQL package. Runs fine as a .dtsx package. (it imports data from Excel to a table)When I import it to a project in 2005 BIDS (no changes) it fails. The error message is "Object reference not set to an instance of an object". Not very informative.If I rename the .dtsx file and import it back into SSIS, it runs fine from SSIS.Same package. Runs in SSIS. Fails in BIDS.I've seen similar questions on the SQLTEAM forum on the same error message, but no answers. Seems like somebody must have had this before and solved it. This server does have a SQL 2000 instance and a SQL 2008 instance. But all my work is in 2005 SSMS, 2005 SSIS and 2005 BIDS. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 12:58:36
|
| Does it start to run or does it fail immediately?Does it use a config file?Do you run it on the same machine in all cases?Does it use some compiled objects.Does the progress show anything?Could be a lot of things.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-16 : 01:10:31
|
quote:
Originally posted by dbthj
I have an SSIS package that runs fine in 2005 SSIS. Runs fine as an SQL package. Runs fine as a .dtsx package. (it imports data from Excel to a table)When I import it to a project in 2005 BIDS (no changes) it fails. The error message is "Object reference not set to an instance of an object". Not very informative.If I rename the .dtsx file and import it back into SSIS, it runs fine from SSIS.Same package. Runs in SSIS. Fails in BIDS.I've seen similar questions on the SQLTEAM forum on the same error message, but no answers. Seems like somebody must have had this before and solved it. This server does have a SQL 2000 instance and a SQL 2008 instance. But all my work is in 2005 SSMS, 2005 SSIS and 2005 BIDS.
might be this??http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/147179bb-9c9f-4d64-bb86-d092f8d702fc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-11-16 : 10:46:56
|
| visakh16, Thanks. That was helpful.That link hit the point that would most be pertinent on this thread.It said, in effect, that the error I mentioned comes when you try to right click on a package and pick "Execute", but that if you first opened the package in the designer and THEN tried to execute it, it would succeed. That is true for most of my packages - Except for the package I am interested in. When opened first and then executed, I don't get that error. It fails. But it doesn't get that error. Exactly why that package fails would probably take V. Studio debugging talents I don't have and it's probably not appropriate for me to ask the forum to fill in the gaps in my programming skills. It does remain, though, that the package succeeds in SSIS and fails in BIDS. The errors this time are:Error at Data Flow Task [Source - Decommissioned$Print_Area [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.Error at Data Flow Task [Source - Decommissioned$Print_Area [1]]: Opening a rowset for "Decommissioned$Print_Area" failed. Check that the object exists in the database.Error at Data Flow Task [DTS.Pipeline]: "component "Source - Decommissioned$Print_Area" (1)" failed validation and returned validation status "VS_ISBROKEN".Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)WHAT THIS PACKAGE DOES: Imports data from two worksheets of an Excel spreadsheet into two separate tables. "Decommissioned" is the name of one of the worksheets and the name of it's target table. The spreadsheet DOES exist and didn't move or change in any way. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-16 : 10:51:25
|
| Does it start to run or does it fail immediately without trying to validate the package?Did you check the progress tab?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-11-16 : 11:08:51
|
| It does start. "Preparation SQL Task" in Control Flow succeeds, then the package dies in the Data Flow. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-16 : 11:44:32
|
| is that sheet getting created in a previous within same package?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-11-16 : 12:08:01
|
| My Mistake. I had renamed the file for some test. That package works if the files exists AND-IF it is first opened in Designer. Otherwise, it gives the error: "Object reference not set to an instance of an object". |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-16 : 12:11:02
|
quote:
Originally posted by dbthj
My Mistake. I had renamed the file for some test. That package works if the files exists AND-IF it is first opened in Designer. Otherwise, it gives the error: "Object reference not set to an instance of an object".
you have set it statically to point to same file itself right? then it would fail if it cant find it during validation.didnt understand what you mean by belowThat package works if the files exists AND-IF it is first opened in Designer.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-11-16 : 13:40:04
|
| Yep. Always uses same filename as source. Doesn't find it if I'm dumb enough to rename it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-17 : 00:56:30
|
| if you want to defer the validation till run time what you can do is to set delay validation property to true for excel source------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Execute in BIDS gives error. OK in SSIS