DTExec Crash (Server upgrade to SQL Server 2008)

Question

                We have started migrating our existing SSIS packages to SQL Server 2008.

We have a DTSX package which makes a MDX query against the cube and processes

that information and inserts that data back into our OLTP database.

 

During migration this package started failing as we used a ADO .NET connection (error code 0x80004002).

I came across this blog http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!1213.entry

which explained that ADO .NET connectors do not work in SQL Server 2008 and

advised me to use a OLE DB connector.

 

I changed that package to use OLE DB connectors instead of ADO .NET. Now when I try to execute

the package, a cmd prompt flashes abruptly and goes off (I managed to finally capture the screenshot,

and it was a SQLDumper). Looks like DTExec crashed abruptly when the SQL connection is being made (using the OLEDB Connector).

 

I tried to execute that package using DTEXECUi.exe and DTEXECUI crashed.  When I tried debugging this issue it

says 'Access Violation...'

As mentioned on this Microsoft KB article (Though this article talks about Oracle connectivity,
I tried to see if this fix works for SQL Server 2008)

http://support.microsoft.com/kb/959794  

I have installed the SQL Server 2008 CU 3 on my box. But even after doing so, this error is still creeping up.

Could anyone help me with this problem please... I am stuck with this thing.

Pratap.  

---

Pratap

Answer 1

Hi,
is this package running fine in BIDS?

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 2

Hi,

  No, it is not working in the BIDS itself.
When I open the OLE DB Connector and click on 'Preview' I get some value
but when I try to do a full run of the package, it comes and stops at the OLE DB Connector.

---

Pratap

Answer 3

Hi Pratap?
What is the query you are giving in the OLEDB source?
Is is a dynamic sp?

---

Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.

Answer 4

No, it is a MDX query.

something like this

"SELECT { [Measures].[xyz]} ON COLUMNS FROM ( SELECT (  [DimTime].[FullDate].&[2008-01-01T00:00:00]  : StrToMember("[DimTime].[FullDate].&[" + Format(DateAdd("d", 1, Now()), "yyyy-MM-ddT00:00:00") + "]"))  ON COLUMNS FROM [CPCube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

---

Pratap

Answer 5

Hi,
Are you using this MDX query in OLEDB source? are you able to parse this query there?

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 6

Hi Pratap,
could you try a simple query just to varify whether it works that way.
Could there be an issue because of the nested query.

---

Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.

Answer 7

Sorry to both Sudeep and ETL Vs ETL for replying so late... Was a bit busy and couldnt get to write a post.

I was finally able to get a work around for this issue and solved my problem for now.

I was able to solve this issue by 
      1. Creating a linked server on my database machine (and making that link point to the analysis server).
      2. Using a OLDEDB connector (in my DTSX package) pointing to my database server (instead of the analysis server)
      3. Using a OPENQUERY as a wrapper around the MDX query which was previously being used in the OLEDB connector to the analysis server.

From all this experience I assume that the SSIS's OLEDB connector's AnalysisServices10.0 provider is buggy (not to mention the ADO.NET providers) and need some fix from the SQL Server team. Hope they realize the pain they are causing developers and release some fix soon.

 Thanks to ETL Vs ETL and Sudeep for trying to understand my problem and spending some of their valuable time analyzing my problem.

Thanks,
Pratap.

               

---

Pratap

Answer 8

Earlier this year, we filed a bug for this topic. The OLEDB for Olap is incompatible with SSIS when querying a cube using MDX.
SQL BU Defect Tracking 450573

We used a linked server with the provider to workaround the problem. The SSIS and SSAS teams were aware of the issue and decided not to fix it in 2005. I can check on Monday if any further progress was made in SSIS 2008, but based on your feedback, it does not sound like it was.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance