Saving Packages and Custom Component data

I am looking for some advice regarding saving custom component data when saving packages.

For custom "properties", this is not an issue, as saving the package will save these properties. Howver, I also have information for each column (besides the properties that columns provides, like Name and DataType) that I need to save if a package were to be saved, and right now it does not save because I am using my own objects to store the data.

I am wondering as to how I can save this information. I have looked up on serialization, but I would like to know if there is another way besides serialization to save this inforamtion as I'd rather not save this to a seperate file.

ThanksI think I found another method, and this method would make more sense...

There is a topic in msdn about persisting custom objects and implementing SaveToXml and LoadFromXml methods. The only problem is that this topic is vague and not very helpful for me in determing how to use this. If anybody can point me to some good documentation, or that could explain it here that would be greatly appreciated.

I have my own class, and I created my own custom object. I have many instances of this object stored currently in a Hashtable. I want to be able to save this.

Thanks|||Search Books Online for "Persisting Custom Objects" (under SSIS). You will either have to implement ISerializable, or write your own routines to save your custom objects as XML.|||Thanks for your reply.

I actually stumbled on a different solution that I have tested and works great.

Because the data that I wanted to save was for each output column, I created custom properties in the IDTSOutputColumn90.CustomPropertyCollection

These custom properties are automatically saved in the XML for you.

Considering that I've been working with custom properties for the actual component I was a little surprise that I did not pick up on creating custom properties for output columns earlier.

Saving a custom object in a sql database table.

Hi,
Using VB.net I have created a custom object (hope my terminology is correct here), it's just a class that defines a few string, integer, and hash table variables. After creating an instance of this class and populating it with data I need to be able to store this instance of my object in a sql server data base table. How do I do this? I saw an article that used the image data type to achieve this (code was in java unfortunately) is this the correct approach. Could you please give me some code examples

Thanks

If you give your class the Serializable attribute, you can bsaically save it to, and load it from a stream, (or string). This means it can be easily saved to a file, database, or even copied across the internet without fear of it being blocked. You can also save it as binary too.

Since you are in effect creating an XML data string when you serialize, you can either save it in an SQL Server 2005 xml field, or into a text field in SQL Server 2000

Have a look athttp://msdn2.microsoft.com/en-us/library/et91as27.aspx

saving a collection of objects to a table

Hello all,
Is there a more efficient way to save a collection of custom objects to a sql server database, other then calling a stored procedure in a loop for each object in the collection?
Any suggestions on how to make this as efficient as possible are welcome.What is the nature of the objects?

If the objects can be serialized to XML then storing the collection as an XML string should work well.|||Actually this is the funny part,
The collection is a result of an xml parsing function. Due to the cost of xml parsing i'm saving
the parse result to a database.
Maybe you could advise on general program design here. I'm very new to the xml thing
and what i need to do is to make a details page of sorts ( which in current form ) at runtime
accesses and parses up to 8 xml files from another server on the network. There is significant
delay between details link click and the actual details page coming up due to the processing taking place behind the scenes, hence my decision to parse once and respond to the subsequent calls with results from the database. The processing involves a number of loops since it is the only logical way i can think of to parse the xml. The only way to parse the xml doc is to
basically loop thourgh it and get what you want am i correct?
Maybe you have and idea for a better quicker design?
thank you for response.|||XML is a good means for exchanging data but as you've seen not necessarily a great means for storing data for quick retrieval. It sounds like parsing once and storing the relevant results into a database for easier retrieval is a good strategy. I've seen sites use this approach for the same reasons you've encountered. (One company's engineers had to "hide" this fact because there was a design dictum to use XML for everything.)

I'm not an expert in XML parsing and ways to improve parsing performance. There's probably a .Net forum for that. I've always tried to keep my XML as straightforward as possible and use it only when a better/faster alternative isn't available.|||ok thanks. I'll just keep plucking along the current path.|||why not parse once and then cache the collection in memory?

this will give even more benefits than accessing a database.|||That would be fine except we're talking lots and lots of items here (more then 15,000) with
up to 7 xml files for each. Do you think it'd still work or would
the server crap out?

saved package gets corrupted

Hi,

I am facing a problem. I have custom data flow transformation.We have saved a package using component's earlier assembly version. Now when we install later version of the component the saved package fails to open. If I try creating new package it succeeds.

Error message :

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (153)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package.dtsx 0 0

We tried overriding "perform upgrade" method but still I am facing the same issue.

Dharmbir

There is a good sample on how to overridfe PerformUpgrade here:

http://msdn2.microsoft.com/ko-kr/library/ms345168.aspx

The minimal thing that you need to do in the PerformUpgrade method is to upgrade the version (see the last line of code in the link above)

Thanks,
Ovidiu

|||

Hi,

I implemented this, still I am getting the error.

If I have earlier version of the Component in the GAC it does not give me the error and component runs. But if I have only the latest version of the assembly in the GAC it gives me error, though I have gaced its policy file also.

thanks

Dharmbir

|||

You can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

sql

saved package gets corrupted

Hi,

I am facing a problem. I have custom data flow transformation.We have saved a package using component's earlier assembly version. Now when we install later version of the component the saved package fails to open. If I try creating new package it succeeds.

Error message :

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (153)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package.dtsx 0 0

We tried overriding "perform upgrade" method but still I am facing the same issue.

Dharmbir

There is a good sample on how to overridfe PerformUpgrade here:

http://msdn2.microsoft.com/ko-kr/library/ms345168.aspx

The minimal thing that you need to do in the PerformUpgrade method is to upgrade the version (see the last line of code in the link above)

Thanks,
Ovidiu

|||

Hi,

I implemented this, still I am getting the error.

If I have earlier version of the Component in the GAC it does not give me the error and component runs. But if I have only the latest version of the assembly in the GAC it gives me error, though I have gaced its policy file also.

thanks

Dharmbir

|||

You can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

Save custom settings in SQL Server Management Studio Express

I use SQL Server Management Studio Express to work in several databases. What gets annoying, is that the databases I work in have 30+ tables in them, only 5-10 of which I actually work with. Then there's the stored procedures I have to sort through to find the ones that I need and so on. I set filters so that I see only what is applicable to me, but I hate having to set those filters every time I start Management Studio back up. Is there a way to save my settings so that it opens the same way every time? I've looked around and can't find an answer to this.

Thanks!

Hi Kevin,

Sorry, but the filters cannot be saved in SQL Server Management Studio. The filter is used temporarily for us to get objects we need.

You may create the objects under some special schema and sort by schema, so that you can find them more quickly.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!