Using SSIS Packages To Process SQL Server 2000 OLAP Objects
Nov 16, 2006
Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP?
It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects. I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000. the DW tool vendor will not support an upgrade to SSAS 2005. We wish to extend the DW from other data sources. My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box.
I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package. Any ideas?
We are converted our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
I've got 51 pretty complex Dts packages that are running on Sql 2000. I'm trying to make them run on Sql 2005 without at first migrating them with the migration wizard, since i know that many of them cannot be migrated.
I'm using the "Execute DTS 2000 Package Task Editor" to make them run in an SSIS package, some of them are running fine, but many of them, especially those with a "Data Driven Query Task" are returning this error:
Error: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
After many research on the web, i've reinstalled the Sql 2000 tools on the Sql 2005 server, but i'm still receiving the same error. Also, i've tried to repair the "Microsoft Sql Server 2005 backward compatibility" in the Add/remove program of the Sql 2005 server without much success.
For your info, i've installed the hotfix Build 2153 on both the server and the client and i'm still having the same problem......
Hi, I have a cube in analysis manager. This cube is processed via a DTS in sql server 2000 with an incremental update. The sql task finishes successfully but there is no data moreover in the cube. So, I proccess manually the cube with the dialog box "process a cube" and I select Incremental Update : the step finishes successfully but there is still not data moreover!! I have to process the cube with a full process each time!! Why that doesn't work?? any ideas??
Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.
I would appreciate and feedback or questions on this migration plan.
Migration DTS 2000 packages to SSIS 2005:
1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package. 2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file. 3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005. 4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file. 5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails. 6. click on the package properties - protectionlevel and change it to dontsavesensitive. 7. right-click and select package configurations..., select to store data creditentials in xml format. 8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful. 9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent. 10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.
I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks
When I start creating a new DTS Package and I choose the Analysis ProcessingTask icon, I only have the option of working with the local MicrosoftAnalysis Server.How can I choose a remote server's Analysis Server in order to process itsdatabase?Thank you
This is more of a philosophical post, but feedbacks are welcome!
I am working at migrating SQL 2000 DTS packages that pulls data from MAS90 via ODBC connections. At first, I REALLY tried to learn SSIS and I hated it at first, with all the new things one has to do to get a simple import to work. After a while, I begin to appreciate some of the new design and the more tiered approach. Indeed, I tried to use SSIS to import the tables and even learned how to overcome the Unicode/non-Unicode conversion errors by using the Import Wizard to do the grunt work.
But today I came across a show stopper: My imports are failing because the source lied about its metadata type and I am getting a "Value too large for output column" error. I tried to recreate the Task to no avail. I searched on the web and there are very few posts regarding to this and unfortunately I don't have a way to tweak my ODBC connection properties for MAS90 to some how "fool" SSIS. I finally give up and migrate the DTS 2000 package instead.
I am not too happy about this solution because I know that more likely or not Microsoft will discontinue support for such legacy approach and then it is more work down the road. I REALLY wanted to do it right, to rebuild it natively in SSIS but why does SSIS have to make things so hard by enforcing the type checks so tightly? Is it so bad to allow users who know the data better to by pass the validations? We are not working in a perfect Comp Sci 101 world where every thing is scrubbed clean, we work in a world of bad, old, malformed data.
If there is a way for me to overcome that "value too large" error, I am all ears. Thank you for reading.
I want to connect MS SQL 2000 Server with Crystal Analysis via OLAP.
I have installed SQL with Windows authentication mode and installed crystal Analysis 9.0
I have configured Crystal OLAP connection setup.
When I am trying to open or create new application through CA I am not getting through OLAP connection with SQL and I am receiving the error as the following.
Error 1
" Unknown User name and bad password."
Error 2
Failes to initialize.( Cannot connect to the server 'XXXX." the server is not started or too busy)
I recently wrote a white paper on OLAP and was having a hard time finding any information about what was new in SQL Server 2000 OLAP on the web. I didn't really find much until I downloaded a trial copy of SQL Server 2000 (which didn't have OLAP, but had links to information on it). We use Hyperion Essbase, and I was wondering if Microsoft OLAP 2000 would be a viable competitor.
How to add a task to copy an OLAP database to a remote Sql server in control flow using SSIS 2005? This OLAP database is existed in the remote server and I just want to replace the existing one with the one just been updated in the SSIS package and the last task in the package is to copy the updated OLAP database and replace the one at the remote server. Hopfully during the copy and paste process it will not screw up the reports that are using the OLAP cubes at the remote server as the report data source. Thanks.
I need to design an SSIS package that processes three cubes within a SSAS database and all their related dimensions.
I also need to ensure this SSIS task is transactional (i.e users are able to browse and query the cube continuously throughout the day...even while the cubes are being processed).
I have noticed that simply adding an Analysis Services Processing Task, and having that task process the 3 cubes with the process options set to "Process Update" and the "Process affected Objects" (visible after clicking change settings) selected DOES NOT force the processing of the dependent dimensions of the cubes.
I have also tried changing the Process Options of the 3 cubes to "Process Full"
I also get errors implying that attribute keys in the fact table are not found in the dimension indicating that the cube is being re-processed before the dimensions have or the dimensions were not re-processed. I came to that conclusion because..If I manually (via management studio) process the dimensions and then the cubes immediately after this error...there are no errors.
I have worked around this by creating two tasks. The first processes the dimensions and the second processes the cubes. This however will not work for jobs run in the day..since it means the cubes may be in an invalid state when the dimensions are being processed.
Has anyone else experienced this? Any advice or standards that might help?
How can we store native .NET objects like arraylists, hashtables in Sql Server? Is this possible? If so, can anybody provide some insight on how to do this?
Hi, i have a ssis package that copies a database using online method of the database transfer task. I realize that this task does not copy my keys and indexes, therefore i went and used the SQL Transfer Object Task which would transfer my keys and indexes, but does not copy the default value of a column, is there something i need to change/turn on for this to work ?
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
What is the security requirement for running this task? I have no problem running this taks as a system administrator but all my users who are in the db_dtsltduser cannot run the same task successfully. They are DBOs in both databases involded in the task. Thank you in advance for your help.
Sometimes at the end of a project you'll end up with unused Tables,Stored Procs, Functions, and Views.Since there is not something like a 'SELECT' trigger ... what is thebest way for telling what is not being used by your solution? To turnon some kind of tracing?Any ideas?Thanks,Kelly GreerJoin Bytes!change nospam to yahoo
On 64 bit SSIS platforms (win2k3 and vista), where the amount of virtual memory is practically unlimited, is there any advantage to running child Integration Services Execute Package Tasks out of process? The question pertains to 64 bit processes on a 64 platform, not 32 on 32, or 32 bit emulation processes on 64.
Would the answer be different based on the amount of physical memory on the machine, supposing one had a "reasonable" amount of memory on the machine (e.g. 4Gb or more )
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.
Hi, I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min. It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation. Also, Whether to use row-level or column-level tracking?
A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?
I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.
I have a SQL Server 7.0 backend server but I am using SQL Server 2000 EM to create DTS packages. Can the DTS packages be opened in SQL Server 7.0? We are contemplating the install of SQL 2000 but we are worried that any new DTS packages created won't work when opened on a SQL Server 7.0 server or that the SQL 2000 DTS functionality is just not compatibile with SQL Server 7.0 and will cause problems.
Right now I built an SSIS package to transform data from external source into local database server. I schedule it to be processed at that database server (ex Server A). Is there any difference performance if I replaced the SSIS package to be processed at another server (ex Server B) ? I'd like to separate the process because I want to reduce workload in Server A by removing the SSIS process to Server B. Am I correct ?
I have Two databases,that same schemas and same tables. that both servers are different location. but data must be update and input from both sites.that data must be same both.
so i've planed to replication data both servers,then i must chose merge replication right?
so i'm set
server1 (assumed server1 as main) are Distributor/Publisher
and
server2 just Subscriber only
that i can configuration successfully.
then i'm chose push replication from server1 but it's not work.
this is error message that show
The process could not deliver the snapshot to the Subscriber.
please advise me what's wrong and how to fix problem?
I have been trying to get a clear explanation on what I need to do in order to be able to execute SSIS packages which I have located on an app server. Is it that I need integration services installed on the app server itself and thats it? Or can someone tell me if there is any more to it, thanks folks!
With DTS you could copy and register some DLLs onto a non-SQL Server PC and run Packages. Is this still possible with SSIS? I imagine that, at the very least, you would have to copy dtexec.exe and the *,dtsx files to the other computer.
I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....
And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.
I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..
I'm running a resource-intensive stored procedure, which reads a filewith about 50,000 lines with a BULK INSERT into a temp table, thengoes through it and inserts a record for each line into another table.While this procedure is running, SQL server stops accepting any otherrequests coming from the website.Question:Is there a way to make SQL server "listen", or emulate an "interrupt"to other requests while in the middle of a long intensive process?I really appreciate your replies.Thank you,Oleg.
Is it possible to import packages in batch into MSDB (in lieu of right clicking and importing each one by one)? We have a lot of packages that are going through a lot of changes so a batch import would save a lot of time.
