SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

this error message has many sources:

• connection is used with user id, and password and not saved
• configuration file does not have the server name
• data flow task is not marked as delayvalidation = true
• low disk space in the server you try to get the data from
• Oracle provider
• Excel 64 provider

I tried to google and found most of the replies are for Oracle and Excel, and sometimes the password. while my case does not include any !!!!!

so I found the delayvalidation and it seemed works but nothing happened. I tried to make the things easier as My package runs in 4 hours and deals with more than 30Million * 200,000 * 1000 records with other tables that compared with the above is look-ups.

what make me confused that it sometimes runs ok, but some other times gives me an error at anytime!!!!

Now, I figured that it may be due to disk space.

Microsoft Analysis Service Performance Final

Final Show of this series, Microsoft does not have a solution for the Large column data size!!!!!! they offer to upgrade the system rather than having a solution.

anyway, the solution is to crack down the dimension so that SSAS can handle it!!!!!!

Microsoft Analysis Service Performance 3

Now a new tip for the SSAS 2005.

1. make attribute relationships
2. remove unused aggregations

Then in any Normal Scenario [according to Microsoft], this will solve it unless you have the [Large Dimension Problem] then it will not affect anything.

the Large Dimension Problem starts when you have at least one dimension with 170,000 records and more. In my case I have 220,000 records So I guess there are no solution.

I read a lot about migrating from SSAS 2005 to SSAS 2008, and also from 32bit to 64bit. but the reviews still as it. it should enhance the memory usage, but the same processing.

another thing that might be interesting, when I browse from Excel 2007 @ the server things are mmm not that bad, but doing the same from a different computer, then the disaster happens when I get to the large dimension.

what Microsoft expect to have small dimensions for making them work fine !!!!!

Microsoft Analysis Service Performance 2

After we got confused, why our cube is too slow when accessing it from other combuters in the same LAN or even when browsing the cube within the same server.

Things to check:

• Make the server 64 bit
• Install SQL server 64 bit

Config to check:

• Use Windows heap instead of SSAS heap

change

MemoryHeapType from 1 to 2

and HeapTypeForObjects from 1 to 0

• Disable the option of blocking other queries if one of them is taking longer time

CoordinatorQueryBalancingFactor from -1 to 1

and CoordinatorQueryBoostPriorityLevel from 3 to 0

This should be done in the msmdsrv.ini file

All this content is coming from Mr. Ashotosh sharma from Microsoft

but till now it didn't solve the problem completely!!! so we are focusing now in the design tips.

Asp.net Update Panel and File upload

Got stuck when using this control in the update panel. and you got a work around by making a pop up window to download the file through it.

This is not the case here, now you can use the ordinary file upload in the update panel! the only thing you have to do is making the button that "submit" to be postback from updatepanel triggers.

< update panel>
< content template>
< /content template>
< triggers>
< ! -- here is your ascync trigger -- >
< / triggers>
< /update panel>


Note: make sure that the control in the triggers tags is visible, if you want to hide it try style="display:none;" attribute.

if you are using a master page that contains an update panel and you want to do it. you can add this line to the page load event:

ScriptManager.GetCurrent(Page).RegisterPostBackControl(Button1);

now it is fine and working properly :)

SQL Server Analysis Server Exploring the studio and troubleshoot

Now we made a cube that wrap all the dimensions and facts we want to analyze in our scope.

The First tab when clicking on the cube is the "cube structure", where you can see the cube structure and modify the relationships and dimensions the cover the cube from the business perspective. you can add a hierarchy, or delete it. you can add new measure, new dimension from the data source view.

The "Dimension usage" tab. Where you can link those dimensions with measures.

The "Calculations" tab . where you can add new calculations to help you to more analyze the data.

The "KPI" tab . where you can add your indicators to see if the data is going fine or there exists something wrong. here you have to know some MDX query language.

The "Perspectives" tab. where you can add perspectives to the cube on each one you will specify the dimensions and the measures related to it.

The Last tab is the "Browse" tab. where you can browse the cube. to do it you have to deploy the cube, and to do so. you have to right click on the project name->click on "properties"-> then change the path of the deployment server. then click ok. then click deploy.

Clarification:

After making the KPI's you may want to see them. There are many tools enable you to browse the cube and manipulate it like: Excel, ProClarity owned by Microsoft now, business Object.

in all of those you may not see the KPI's as you saw them in the Visual studio. Why? because this is an owned shapes by visual studio, you may see this KPI's with different shapes and colors. so according to the viewer, you will design the KPI indicator shape to fit the viewer. Otherwise you have to develop this viewer to the target tool.

Troubleshoot:

"A connection cannot be made. Ensure that the server is running. "

this error message occurred for the impersonation of the SQL server instance.
the solution is:
1- in the project properties, deployment section, modify the server to the SQL server 2005 instance.
2- in the data source double click, in the impersonation tab, select the "use the service account".

Now you have ended the cube and we wanna view it.
The next time we will explore the Reporting Service structure.

SSIS Troubleshoots and Bugs

ah, Now you face some troubles, before we proceed, you have to install SQL Server 2005 SP2. This Service Pack fixes alot of bugs. and also generate another. Anyway, after you install it you might face one or more of the following:

1- Could not add the DataFlow task?

check the SQL Server running service. Open the "Administrative Tools->Services-> SQL Server Integration Services " and change the logon to "Local System Account", then restart the service.

also check that most of the services uses the "Local System Account", not the network account.

this problem occurs when you are in a network, the Sql server uses this account as a general user to work with. But this is wrong

2-Error [OLE DB Destination [2923]]: The column cannot be processed because more than one code page (1252 and 1256) are specified for it.

this happens with the OleDB module that transfers the data into the destination DB.

the problem is the code page is differ, the problem has two solutions:

a. put a "derivative column", and convert all columns code page to the DB server code page.
b. go to the destination [Ole Db Destination] and check "always use default code page" to true.

3- I drawn my transformation but when I replaced it I got error?


A general bug. when you drag a component fromthe toolbox and drop it into the board. It takes a number. if this component generates an elements like the datasources, each element took a number. the problem is this number is unique. and cannot be reallocated to another unit.

the solution redraw the package....

4- Fuzzy lookup somehow took so long and other times does not take time?

the reason is simple, your data contains duplicate rows.

the Fuzzy lookup component get the data, if the data duplicated it builds another indeces. and allocate it to the rows. the do the fuzzy operation. when you try to apply the fuzzy algorithm to duplicated rows, it doubles the effort for ignoring the result of this row values.

Next time for the SSAS