Solved

Problem saving SSIS Package to SQL Server

Posted on 2010-08-13
20
1,930 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can > be used for a Text field 6 46
sql server service accounts 4 26
Loops and updating in SQL Query 9 29
access query to sql server 3 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

778 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