SSIS Data Flow task not completing, but not failing
-
Apologies for the ambiguous title, but was unsure how to headline this query. Windows Server 2008 (x86) with SQL Server 2008, posted images of the task operational details are below, but first a summary of the issue.
I have a very straightforward data flow task to SELECT source data from a PI Wadis database table (access through an ODBC driver installed on the server) and INSERT into an existing SQL 2008 db table. The task is very basic, in theory, so for efficiency was created using the Import and Export Data (32-bit) wizard. On completion, the process was initially ran and executed successfully.
But, when I now try to edit the data flow task in VS2008 BI, the process executes only so far but then halts.
It does not fall over or fails, just halts. The ADO.NET Data Source editor using the ODBC driver (referenced, as ‘PI 1’ & SQL command will preview the correct data. The OLE DB SQL Data Source editor will preview the correct data. The task itself will not complete.
I’ve tried various ‘tweaks’ but cannot get this to work – really stumped. Any suggestions welcome, and feel free to ask about anything I have not covered that may help.
Thanks!
-
Have you checked the target and source servers to see if the activity is being held up by something normal like lock/block issues?
On the SQL server side, check sp_who2, or the Activity Monitor in SSMS, and see if your insert statements are making it there, and are being blocked by anything. Should be able to do something comparable on the source server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks for the replt GSquared.
I have checked activity logs on the destination server and nothing is getting there. The diagramatic display of the package shows the first task as active(yellow), and that's how it stays.
The source server is inaccessible to me.
Locks wise, I have tried all locks - just to see if one returns a result - but still the same happens.
-
Actually, if the source server is staying yellow, then that's not finishing. It's never getting to the transmit step. That means the problem is the data source.
I'm not familiar with "PI Wadis database" sources. I tried searching for that phrase online, and the closest I get is something about phone numbers, but it doesn't look like a database product. What am I missing there?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Here's their website: Osisoft.com products
I'm not massively familiar with all that PI stuff either, all I know is I need to get data from this & have done so through MS Access & successfully as a standalone process with the Imp/Ext Wizard. What is the difference between how that process works, and an SSIS task?
Thanks for continued replies
-
When you say "Import/Export Wizard", do you mean the one in SQL Server Management Studio (SSMS)?
If so, you have an option in that wizard of saving the import/export package. What that does is save an SSIS package. You can save one from that, and then use that to build out a more fully functional SSIS package. The advantage is that you know it works and has the right settings, if the wizard worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
That is exactly what I did, and the wizard executed and saved exactly as planned.
It's when I opened the package to build more functionality that the issue occurs.
I've had many suggestions from various sources around permissions, but I've pretty much discounted that because:
1. The Improt/Export wizard runs OK.
2. It uses a locally installed ODBC driver with the connection details, including credentials
3. I can replicate the link and update exactly with a local MS Access table - this in fact, is my workaround at the moment, importing the data to Access and SQL Server extracting from that table - not ideal.
hmmmmm :unsure:
-
It all sounds right. No suggestions just spring to mind. If it were mine, I'd be playing with various aspects of it to see what I could break/unbreak. Wish I could help more.
One thing I'd test is, can the SSIS package export to text instead of SQL Server as the destination. Just shooting in the dark, but it would eliminate one variable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I'd be interested in knowing if there is some activity on the export server that is using up CPU time, like a hung application not related to the database service. I read you have limited access here but maybe another user can check for you.
Also,if I understood correctly, you successfully transferred data from this PI database to MS Access. If you need this done quickly and dirty you can try doing the same and then importing from Access into SQL Server.
MY quick thoughts
----------------------------------------------------
-
I had a problem with a hanging task earlier this week. After some head scratching, I instructed the ops to delete the job and everything related to it, and recreate all from TFS.
The job now runs and completes.
-
Thanks for the comments. Thought I'd just drop you an update to say this hasn't gone away. As the MS Access workaround is working, I can only assume I missing something with the connection to the source PI db.
I have a case open with the vendor and will post the result here when I get this sorted.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply