Solved

Problem saving SSIS Package to SQL Server

Posted on 2010-08-13
20
1,849 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now