July 19, 2012 at 10:11 am
Hi friends,
i tried to run the SSIS package in the production server but i get the following error.
[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the database with the ID of 'MyDB' does not exist in the server with the ID of 'WINDOWS-IMT5PR2', or the user does not have permissions to access the object.
Help me friends..
Thanks,
Charmer
July 19, 2012 at 10:20 am
Did you use a job to run it or did you manually hook to the production databases to try to run it?
If it's you, you probably don't have the rights on that database, talk to the DBAs.
If it's the job on production, check the login rights for the SQLAgent against that database.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 19, 2012 at 11:53 am
Evil Kraig F (7/19/2012)
Did you use a job to run it or did you manually hook to the production databases to try to run it?If it's you, you probably don't have the rights on that database, talk to the DBAs.
If it's the job on production, check the login rights for the SQLAgent against that database.
I tried myself, Evil....
i made this package on my local machine and i tried to run on the production server.....
Production server is holding a database which is having same structure as like as my local database. but when i try to run it on production server, it still looks for my local database....
the error i posted above shows 'MyDB' (my local database) ....actually it must look for ToSaDB....i even changed the connections and tested it...it was successful...but during run time, it keeping looking for my local database...
i don't know where problem is relying on...
help me Kraig...
Thanks,
Charmer
July 19, 2012 at 12:21 pm
Charmer, bear with me. I'm afraid I'm having a little trouble deciphering what you mean here so I'm going to answer each part with how I understood it. If it's overkill, hopefully it'll help us get to what you're trying to get me to understand.
Charmer (7/19/2012)
i made this package on my local machine and i tried to run on the production server.....
A few questions to help you troubleshoot this. You created the package locally. No problem. You tried to run on production... does this mean you Remote Desktop'd to Production and loaded the package up there, or you changed the connections in your local Visual Studio to look at the production server?
Production server is holding a database which is having same structure as like as my local database. but when i try to run it on production server, it still looks for my local database....
This sounds like a configuration piece. Have you applied configurations to this package? Check the menu option: SSIS - Package Configurations... and see if anything is listed.
the error i posted above shows 'MyDB' (my local database) ....actually it must look for ToSaDB....i even changed the connections and tested it...it was successful...but during run time, it keeping looking for my local database...
i don't know where problem is relying on...
This, however, sounds more like you've copied your package over to the production server, opened up VS there, and attempted to run it without adjusting your connections. The connection in the copied dtsx package will need to be adjusted to have the correct database name, as well as the correct server connection information unless it's set as (local)... but even then, with two different database names a direct copy will never function correctly without configurations and even then you'd be forced to alter the initial catalog component of the configuration.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 19, 2012 at 10:06 pm
Do you use any variables to update the data source? You might want to check if you've set the data source to update it's connection string/database information to a specific variable. You should see a section for either variables/parameters/function or something in that nature in the properties of the data source.
If so, opening up the data source and updating it won't keep the changes when you run it since the connection string will get updated with your variable during run time. If you do have the data source tied to a variable, you should be able to just update the variable to point to the right data source.
July 20, 2012 at 3:35 am
Sorry Kraig, let me make you clear...
i created this package on my local and executed successfully...
the same thing i was trying to do on the production server, what i did was, i took the complete package and put on production server, and opened VS and tried to run it after changing the (proper) connection details......
but it failed....it still looking for my local machine's database....i did not create any config file even on my local...and no where...but how could this connection is keep looking at my local database though after changing the connection..?
I am really confused , Kraig...
Thanks,
Charmer
July 20, 2012 at 3:36 am
richykong (7/19/2012)
Do you use any variables to update the data source? You might want to check if you've set the data source to update it's connection string/database information to a specific variable. You should see a section for either variables/parameters/function or something in that nature in the properties of the data source.If so, opening up the data source and updating it won't keep the changes when you run it since the connection string will get updated with your variable during run time. If you do have the data source tied to a variable, you should be able to just update the variable to point to the right data source.
No , I am not using any variables at any where...
Thanks,
Charmer
July 20, 2012 at 5:40 am
On the copied package, can you open up the DDL task and make sure that the XMLA script you're using is pointing to the right database.
If you're using one of the Analysis Services components, you will need to open up the task and re-point it to the right database.
The Analysis Services processing tasks use the data source to establish a connection into the server, but uses the database referenced in the task itself to determine what to process.
September 22, 2014 at 7:57 am
Is this for SQL Server 2012? (It says 2005 at the header of this thread) I am having the same issue, so following. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply