How to deploy assemblies to SQL Server 2005

I'm investigating the possible problems of transition of my project to
SQL Server 2005.
I'm planning to intensively use the CLR integration as it gives a lot
of benefits.
Here is the problem: to deploy the compiled CLR assembly to SQL Server
I have to put it on the shared location that is visible from the SQL
Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
The question is: In general I won't have such a "shared location" as
the SQL server may be "on the other side of planet", I mean it may not
have access to the shared folders the client may access. My program
will be distributed all over the world, it should work in different
environments, so I cannot rely on the shared location. Is there any
other method to deploy the assembly to SQL Server? This is the only
true problem as far as I can see that stops me from starting the
transition.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1105634014.804208.150320@.c13g2000cwb.googlegr oups.com...
> I'm investigating the possible problems of transition of my project to
> SQL Server 2005.
> I'm planning to intensively use the CLR integration as it gives a lot
> of benefits.
> Here is the problem: to deploy the compiled CLR assembly to SQL Server
> I have to put it on the shared location that is visible from the SQL
> Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
> The question is: In general I won't have such a "shared location" as
> the SQL server may be "on the other side of planet", I mean it may not
> have access to the shared folders the client may access. My program
> will be distributed all over the world, it should work in different
> environments, so I cannot rely on the shared location. Is there any
> other method to deploy the assembly to SQL Server? This is the only
> true problem as far as I can see that stops me from starting the
> transition.
>

How to deploy assemblies to SQL Server 2005

I'm investigating the possible problems of transition of my project to
SQL Server 2005.
I'm planning to intensively use the CLR integration as it gives a lot
of benefits.
Here is the problem: to deploy the compiled CLR assembly to SQL Server
I have to put it on the shared location that is visible from the SQL
Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
The question is: In general I won't have such a "shared location" as
the SQL server may be "on the other side of planet", I mean it may not
have access to the shared folders the client may access. My program
will be distributed all over the world, it should work in different
environments, so I cannot rely on the shared location. Is there any
other method to deploy the assembly to SQL Server? This is the only
true problem as far as I can see that stops me from starting the
transition.Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1105634014.804208.150320@.c13g2000cwb.googlegroups.com...
> I'm investigating the possible problems of transition of my project to
> SQL Server 2005.
> I'm planning to intensively use the CLR integration as it gives a lot
> of benefits.
> Here is the problem: to deploy the compiled CLR assembly to SQL Server
> I have to put it on the shared location that is visible from the SQL
> Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
> The question is: In general I won't have such a "shared location" as
> the SQL server may be "on the other side of planet", I mean it may not
> have access to the shared folders the client may access. My program
> will be distributed all over the world, it should work in different
> environments, so I cannot rely on the shared location. Is there any
> other method to deploy the assembly to SQL Server? This is the only
> true problem as far as I can see that stops me from starting the
> transition.
>

How to deploy assemblies to SQL Server 2005

I'm investigating the possible problems of transition of my project to
SQL Server 2005.
I'm planning to intensively use the CLR integration as it gives a lot
of benefits.
Here is the problem: to deploy the compiled CLR assembly to SQL Server
I have to put it on the shared location that is visible from the SQL
Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
The question is: In general I won't have such a "shared location" as
the SQL server may be "on the other side of planet", I mean it may not
have access to the shared folders the client may access. My program
will be distributed all over the world, it should work in different
environments, so I cannot rely on the shared location. Is there any
other method to deploy the assembly to SQL Server? This is the only
true problem as far as I can see that stops me from starting the
transition.Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1105634014.804208.150320@.c13g2000cwb.googlegroups.com...
> I'm investigating the possible problems of transition of my project to
> SQL Server 2005.
> I'm planning to intensively use the CLR integration as it gives a lot
> of benefits.
> Here is the problem: to deploy the compiled CLR assembly to SQL Server
> I have to put it on the shared location that is visible from the SQL
> Server 2005 side, and then run "CREATE ASSEMBLY..." statement.
> The question is: In general I won't have such a "shared location" as
> the SQL server may be "on the other side of planet", I mean it may not
> have access to the shared folders the client may access. My program
> will be distributed all over the world, it should work in different
> environments, so I cannot rely on the shared location. Is there any
> other method to deploy the assembly to SQL Server? This is the only
> true problem as far as I can see that stops me from starting the
> transition.
>

How to de-normalize one to many relationships?

I am a newbie to SSIS. I have been working through SQL Server 2005 Integration Services and am quite pleased with the book and with SSIS. That said, I am having trouble determining how to handle a challenge.

The business challenge is that we need to push new inventory items that we sell from our back end accounting system onto our web site. The challenge is that each item can show up in 1 or 100 categories. The silly web software wants us to write the item and the categories that item is associated with in one transaction / command.

Stated another way, when we push an item into the web store, we need to push the item and an array of categories in the same transaction.

This is my technical challenge, I cannot figure out how to select 100 records out of an item table in MS-SQL and then create an array of categories for each of those 100 items. (items belong to >1 categories)

I thought I could use two OLE DB sources where the main source was the item table and the 2nd source was the category table.

My best guess at this point is to use an OLE DB source for the item table. Then use the script component and hard code the read from the category list within the script component.

As a note, scalability is not really an issue, there would be no more than 10-20 items being pushed at any given time.

Any help would be GREATLY appriciated.

It sounds to me like you need two dataflows: the first to insert the new categories, and the second to lookup the new categories and insert the items. This can be done in a single transaction by setting RetainSameConnection=True on the connection manager for your destination connection, and placing Execute SQL Tasks before the first dataflow and after the second to execute BEGIN and COMMIT TRANSACTION statements respectively.
|||JayH - Thank you for the reply. My original post was not as clear as it should be.

First, our destination is actually a web service which we are accessing via a proxy / assembly. We call the assembly from a reference inside the script component. The goofy web service wants us to write the item record along with the categories to which the item belongs. The categories is basically an array that has to be declared.

Second, the categories are already defined on the web site. Each category has its own category ID. When we write the item record, we reference an array of category id so the web site software understands the relationship between the item and the category.

From a pseudo code perspective:
1) Select items from the item table.
2) Process each item one at a time.
3) For each item, find the associated category id's
4) Count the number of category id's for each item
5) Declare an array of category id's for each item using the count from #4 above
6) Populate the array with the category id's
7) Populate the "item record" with the category id array.
8) Call the web service via the assembly

Yes, the web service I am forced to use is a bit goofy but I cannot change it. :-(

Thank you for any thoughts...
|||Okay, I have a better picture now. I'm still not sure about where the item/category relationship is coming from, but if you can get the rows in an order like the following, then you should be able to create the array pretty easily in script.

Item 1 Category 1
Item 1 Category 2
Item 1 Category 3
Item 2 Category 3
Item 2 Category 2

If the data enters the script sorted by Item, then the script can evaluate each row and build an array of Categories for each item. When the Item changes from the previous row, you know you have all the Categories and you can make your web service call. The last row condition would need special handling and for that you should override the FinishOutputs method in your script so you can call the web service for the last Item.

Let me know if that sounds closer to what you're looking for.
|||JayH,

Yes, you have done a better job of explaining my issue that I have. I believe I comprehend the concept.

I am working on your concept now.

Be back later today. With me luck!

I am so remedial in SSIS...