Saving a DTS pakage to MetaData Services

Question

Post reply

Saving a DTS pakage to MetaData Services

Marysa

Ten Centuries

Points: 1023
More actions
June 15, 2005 at 9:42 am

#93208

I'm trying to save a DTS package to Meta Data Services but don't have the option of Meta Data Services in Location, when doing a Save as...
Can someone point me in the right direction on how to set this up?

SQL Server Standard\Enterprise 2000 SP3

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Francisco Javier Valdebenito Mr or Mrs. 500 Points: 570 More actions · Answer 1

If I remember well, SP3 Block the option to save to metadata repository, It's for a security reason.

I don't know how to enable again.

Francisco

Grant Parks Old Hand Points: 364 More actions · Answer 2

Go up to the Data Transformation Services node in the console tree (the parent of Local Packages), right-click, select Properties, and select "Enable Save To Meta Data Services"

Teague Byrd Right there with Babe Points: 752 More actions · Answer 3

Grant is right, you will need to check that "Enable save to Meta Data Services"

In my case, there was also a rights issue; maybe related to SP3 as noted above. Check with the DBA.

Teague

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

I had this same problem the other day. So, I tried to check the box in the properties and when I said ok, it said it wasn't able to update. Why is that?? So, when you save it to SQL SERVER, where does it go???? Since I couldn't save it to Meta Data, I chose SQL SERVER and thought that it would be in the 'LOCAL PACKAGES' node and no....but where did it go?????

Thank you!!,

Angelindiego

Teague Byrd Right there with Babe Points: 752 More actions · Answer 5

Hi Dana,

You might go back an look for that package again. I have found that SQLServer is sometimes pretty bad about that whole "What you see is what you get" thing. The package might have indeed gotten saved but if you didn't physically click refresh, it might not show up. There are several other places where SQLServer does this and even though I know about it, I will forget and get all anxious and panicky when I think I have lost something.

I don't know if this was your case; but there is a problem if you do the following: Save to Meta Data Services, then save the same package to a Structured Storage File, then open the Structured Storage File and try to save back to Meta Data Services. Something changes and the Meta Data Package doesn't recognize its own baby. If you delete the package from the Meta Data Services Packages, you can then open the structured storage file and save it as a Meta Data Services Package.

It's Friday!

Teague

Angelindiego SSCertifiable Points: 6048 More actions · Answer 6

Hi Teague!! Yes, it is Friday, thank Goodness!!!!!

OK, I remember the option of saving to a structured sql file....but with that or saving to SQL SERVER, WHERE are they saved to????? I did a search trying to find my dang package and couldn't find it!! What I would like is to see it saved as a package in the Local Packages node.....any ideas???

PS. Enjoy your weekend!!

Thank you!!,

Angelindiego

Teague Byrd Right there with Babe Points: 752 More actions · Answer 7

Oh, you mean where does it go physically?

I found this on Books On Line:

If you save to SQL Server, the DTS package goes into the sysdtspackages table in the msdb database as BLOB data.

I don't know that you can actually do anything with it in that form without some fancy tool.

Teague

Angelindiego SSCertifiable Points: 6048 More actions · Answer 8

Geez Louise!!!! What is the point, she asks!! Ok, I will try this again...and see if I can then re-save back to Meta Data Services. This sounds like a BIG BAD BUG in DTS!! I want to save package, and have it go to Local Packages. Is this too much for a girl to ask???? Thanks Teague!!

Thank you!!,

Angelindiego

Teague Byrd Right there with Babe Points: 752 More actions · Answer 9

Hi,

Another thought. (I only get them one at a time)

You might check to make sure you are a user with all the appropriate rights on the MSDB database. I'm not sure how tight your DBA has everything tightened down security wise.

Teague

Angelindiego SSCertifiable Points: 6048 More actions · Answer 10

OK...think hard, and get another thought. I saved that package yesterday as Dana Test and it isn't in the syspackages table. The little bugger has escaped!!

Thank you!!,

Angelindiego

Teague Byrd Right there with Babe Points: 752 More actions · Answer 11

Hey,

Ok, this is the last thought for this week. I don't do any thinking on the weekends.

I have done this before so check it.

Sort your packages by date and check the last few. If you had a typo, maybe it got saved as something like 'ana Test'

Something else I have done:

If you are working in a multiple server environment, make sure you were on the server you 'thought' you were when you saved.

Only one more hour for me!

Have a good weekend.

Teague

Angelindiego SSCertifiable Points: 6048 More actions · Answer 12

Leaving in an hour...lucky you!! I have checked the sysdtspackages table and it isn't there, in any shape or form. There are package eating gremlins in there for sure! It was just a test package, so I can play some more and see what I can do. No thinking over the weekend, but if you wake up in the middle of the night with a brain storm, write it down on a piece of paper (can you tell I have done this??) and holler at me on Monday when your thought processes start in again!!

Thank you!!,

Angelindiego