Solved

Problem saving SSIS Package to SQL Server

Posted on 2010-08-13
20
2,048 Views
Last Modified: 2013-11-30
I am using the import wizard to try to save a package to sql server.  I get the error 'The attempted operation is not supported with this database version' in the save step.  I did some research and found out that the problem lies in that I have a instance name attached to the server name.  It said to change the configuration of the file MsDtsSrvr.ini.xml.  I did as it said changing the <ServerName> to ServerName\InstanceName.  I restarted SSIS in the configuration manager and tried to save again.  No luck.  Anybody have any ideas?  Using 2008 SQL Server Managment Studio and server is SQL Server 2005.
0
Comment
Question by:bamoon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 5
20 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 33429279
Try and use the management studio to connect to the SSIS.

The SSIS is the server name. What  you defined in that XML is the SQL database engine store for the packages.

SSIS deal with 3 different stores: SQL, File and DTS

Be sure not to mix SSIS server with SSIS store...and each store's path to their packages
0
 

Author Comment

by:bamoon
ID: 33429431
I went into DTS through the management studio and tried use the import package files. I previously saved the packages as files.  I think this is what you meant? This only gives me the option of importing DTS 2000 Package Files (.dts) the files I am trying to import are .dtsx.
0
 
LVL 18

Expert Comment

by:x-men
ID: 33429708
I'm no quite following you on that DTS2000 packages only...try using the DTUTIL.exe

It's a command line utility to manage dts packages

youll find the syntax and examples here:
http://msdn.microsoft.com/en-us/library/ms162820.aspx
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Expert Comment

by:x-men
ID: 33429716
...you're not talking about the legacy folder under SQL Server Engine are you?
0
 

Author Comment

by:bamoon
ID: 33429733
Yes I was, I don't think that's what I want is it?
0
 
LVL 18

Expert Comment

by:x-men
ID: 33429774
no, you whant to connect to a Integration Services Instance:

File - Connect Object Explorer :
Server Type: Integration Services
Server NAme: your SSIS server
.....
0
 

Author Comment

by:bamoon
ID: 33429911
OK I did that and it opened up a SSIS with my computer name.  I was able to load the files into the File System folder under stored packages. Can I now write an sp that will run these or how best to do this?  My end goal is to have the user click a button in an Access front end and run these.
0
 
LVL 18

Expert Comment

by:x-men
ID: 33429973
yes, you're set.

try and add a SQL Agent Job, of the type SSIS and navigate to it. If you're able to do that, then everything is set.

0
 
LVL 18

Expert Comment

by:x-men
ID: 33429981
"to it"...to the package, I mean.
0
 

Author Comment

by:bamoon
ID: 33431736
It seems I don't have permissions for the SQL Server Agent.  What role do you have to be in to use it?
0
 
LVL 18

Expert Comment

by:x-men
ID: 33443932
you need to have access to the msdb database and execute permissions on the stored procedures like sp_%job%
0
 

Author Comment

by:bamoon
ID: 33530549
This did not work.  I cannot save the package on the sql server because the above work around MS provided for multiple instances does not work, at least for me.  I cannot access the package on the local machine from the job I set up in Sql Server Agent.  I doubt that would be a feasible option since I need others w/o SSIS to access it. I have been trying to execute the package through the file saved on the network using the master..xp_cmdshell 'dtexec /f command. That is not working.  I've exhausted my options.  I don't know why this is so difficult.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33601724
>>using the master..xp_cmdshell 'dtexec /f command. That is not working. <<
That is because as you have discovered ("I cannot access the package on the local machine from the job I set up in Sql Server Agent") SQL Server cannot access your SSIS package.  The correct way to do this is by running the DTEXEC utility.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33601726
And this:
The correct way to do this is by running the DTEXEC utility.
Should have read:
The correct way to do this is by running the DTEXEC utility from the command line and not using xp_cmdshell.
0
 

Author Comment

by:bamoon
ID: 33625992
I believe this will work on my machine but what about others who do not have Sql Server?  Right now I am the only person who can perform the monthly imports that are needed.  That is not the ideal situation.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33632737
I agree.  But it all depends on who has access to the SSIS packages and who has licensed copies of MS SQL Server.
0
 

Author Comment

by:bamoon
ID: 33665423
I am the only one with MS Sql Server.  The others have access to the DTS files saved on the network but without Sql Server it not going to help.  I have tried to save these packages on the server but keep getting the error.. The attempted operation is not supported by this database version.  I am using MS Sql Server 2008 but the database is on a Sql Server 2005.  Is there a way to make this work?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33668109
>>The others have access to the DTS files saved on the network but without Sql Server it not going to help. <<
I agree somewhat. And it is dtsx and not DTS (DTS is for SQL Server 2000).

>>Is there a way to make this work?<<
If you can execute the dtsx files on the server, one approach you could take is use something like PSExec to execute them from any wokstation.  This way you would not have to purhase a SQL Server license for anyine executing them.
0
 

Accepted Solution

by:
bamoon earned 0 total points
ID: 33781615
I am getting the database moved to another server where I can have admin rights to resolve this problem.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33784795
>>I am getting the database moved to another server where I can have admin rights to resolve this problem.
...
The responses while welcomed were not able to solve my problem.  Thanks.
<<

That is too funny!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql and asp dates 5 41
Server 2012 r2 and SQL 2014 6 33
SQL syntax for max(date) 3 35
T-SQL: Wrong Result 7 35
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question