Problem saving SSIS Package to SQL Server

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.
bamoonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

x-menIT super heroCommented:
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
bamoonAuthor Commented:
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
x-menIT super heroCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics ā€“ known as key performance indicators (KPIs) ā€“ for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

x-menIT super heroCommented:
...you're not talking about the legacy folder under SQL Server Engine are you?
0
bamoonAuthor Commented:
Yes I was, I don't think that's what I want is it?
0
x-menIT super heroCommented:
no, you whant to connect to a Integration Services Instance:

File - Connect Object Explorer :
Server Type: Integration Services
Server NAme: your SSIS server
.....
0
bamoonAuthor Commented:
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
x-menIT super heroCommented:
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
x-menIT super heroCommented:
"to it"...to the package, I mean.
0
bamoonAuthor Commented:
It seems I don't have permissions for the SQL Server Agent.  What role do you have to be in to use it?
0
x-menIT super heroCommented:
you need to have access to the msdb database and execute permissions on the stored procedures like sp_%job%
0
bamoonAuthor Commented:
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
Anthony PerkinsCommented:
>>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
Anthony PerkinsCommented:
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
bamoonAuthor Commented:
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
Anthony PerkinsCommented:
I agree.  But it all depends on who has access to the SSIS packages and who has licensed copies of MS SQL Server.
0
bamoonAuthor Commented:
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
Anthony PerkinsCommented:
>>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
bamoonAuthor Commented:
I am getting the database moved to another server where I can have admin rights to resolve this problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.