CLONING PACKAGES

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Besides copying the .dtsx file and editing the copy to suit your needs?|||You can create a program to generate SSIS packages. If you want to reverse your current packages into C# code, take a look at http://www.ivolva.com/ssis_code_generator.html.|||You could also create a template for each type of load process and store them in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder. I think that's the correct place.|||Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.|||jwelch, that's an interesting solution. I'll try to play with you and let you know if it works. But it seems it's what I need.|||

LoveDanger wrote:

Phil Brammer: Yes, I want to be able to change all hard-coded string dynamically. Assuming I use standard naming convention all object names will have the same structure. All I want to do is loop through table names and replace appropriate strings within the existing template package to generate new packages on the fly.

Using expressions, you can build just ONE package to do this though.

Provided the structures of all of the tables (both sources and destinations) are all the same... That is, you can dynamically adjust the tables the OLE DB sources and destinations go against.|||Thanks Phil, this should work. I assume the transformation part would be tricky (I'm not sure how to map all the columns dynamically), but I'll check if that could be done. Also, I believe the solution posted by jwelch (http://www.ivolva.com/ssis_code_generator.html) should work if one still wants to generate numerous packages.

Thank you all!
|||Note: My solution works if you are looping through tables that have the same structure. If they don't, then my solution won't work. Once you build the package (mappings and all) you can start using expressions to dynamically change the table names.|||Yeah, in my case we have different set of attributes for each dimension that I'm not sure how to handle. I used your approach with BCP though - use only one package to transfer data from a number of flat files to SQL Server tables. Though you still have to generate format files for each that pretty much translates your column mappings.|||

LoveDanger wrote:

I loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.

Are there any analogous solutions for SSIS? I cannot find anything.

My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don’t have to go manually through each of them to replace certain items such as stored procedure names, etc.

Thank you!

Sounds to me like you want to use templates. Matt explained where to drop them elsewhere in this thread.

-Jamie

|||

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

Does template allow you to do search and replace for the entire package?

|||

LoveDanger wrote:

Well, I am not really sure how would templates solve my problem. I will still have to go through each package task and replace the names of the objects manually.

It sounds as though you'll have to do that regardless of the solution though?

LoveDanger wrote:

Does template allow you to do search and replace for the entire package?

No, not really. You could open up the package's XML (right-click on the package and select 'View Code') and do a Find-Replace that way of that's what you want to do.

-Jamie

|||Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia|||

LoveDanger wrote:

Thanks Jamie, I think this would be the easiest in my case. At least it will save time on going through each task manually.

Anastasia

Cool. Take a copy of the package before you alter it

Client Side Sequential Guid Generation

Is it possible to generate a Guid on the client side similar to
NEWSEQUENTIALGUID()?
I need a solution to PK generation that benefits from Guid so i can create
an object graph on the client side without the performance problems of
standard Guids.
Master-Detail data will be inserted into a Dataset and posted to the
database in one transaction to save round trips in a web service
application.here is from an old post:
http://groups.google.co.uk/groups/s...ql+oj+dllimport
-oj
"Anthony" <aa@.noreply.com> wrote in message
news:%233QzigLeGHA.3888@.TK2MSFTNGP04.phx.gbl...
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>|||Hi Anthony
If you want to identify the individual client then you a GUID would not give
you that information, therefore you would probably end up storing that as a
separate column which is part of a composite key. For SQL Server the other
column could be an Identity which would give you incrementing numbers and if
you needed these to be contiguous then they could be ranked. The other
alternative would be to maintain a counter for each client that you incremen
t.
John
"Anthony" wrote:

> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>
>|||It depends on the application. If it's a .net app, then it's very easy to
generate the guid by using the Guid.NewGuid() method. (It sounds like it
from the use of DataSet, which is a .net class.)
There should be a way from other languages to generate a guid.
"Anthony" <aa@.noreply.com> wrote in message
news:%233QzigLeGHA.3888@.TK2MSFTNGP04.phx.gbl...
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>|||Sorry, I mean I NEWSEQUENTIALGUID() was introduced to counter the
performance problems of the standard Guid. BUt I would like my Id
generated within my .NET app.
Anthony wrote:
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.

Client Side Sequential Guid Generation

Is it possible to generate a Guid on the client side similar to
NEWSEQUENTIALGUID()?
I need a solution to PK generation that benefits from Guid so i can create
an object graph on the client side without the performance problems of
standard Guids.
Master-Detail data will be inserted into a Dataset and posted to the
database in one transaction to save round trips in a web service
application.here is from an old post:
http://groups.google.co.uk/groups/search?hl=en&q=sql+oj+dllimport
-oj
"Anthony" <aa@.noreply.com> wrote in message
news:%233QzigLeGHA.3888@.TK2MSFTNGP04.phx.gbl...
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>|||Hi Anthony
If you want to identify the individual client then you a GUID would not give
you that information, therefore you would probably end up storing that as a
separate column which is part of a composite key. For SQL Server the other
column could be an Identity which would give you incrementing numbers and if
you needed these to be contiguous then they could be ranked. The other
alternative would be to maintain a counter for each client that you increment.
John
"Anthony" wrote:
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>
>|||It depends on the application. If it's a .net app, then it's very easy to
generate the guid by using the Guid.NewGuid() method. (It sounds like it
from the use of DataSet, which is a .net class.)
There should be a way from other languages to generate a guid.
"Anthony" <aa@.noreply.com> wrote in message
news:%233QzigLeGHA.3888@.TK2MSFTNGP04.phx.gbl...
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.
>|||Sorry, I mean I NEWSEQUENTIALGUID() was introduced to counter the
performance problems of the standard Guid. BUt I would like my Id
generated within my .NET app.
Anthony wrote:
> Is it possible to generate a Guid on the client side similar to
> NEWSEQUENTIALGUID()?
> I need a solution to PK generation that benefits from Guid so i can create
> an object graph on the client side without the performance problems of
> standard Guids.
> Master-Detail data will be inserted into a Dataset and posted to the
> database in one transaction to save round trips in a web service
> application.

Client Connection Problem.

Hi Everyone,
I have a problem while executing my application on the network. I don't know
why this error "cannot generate SSPI context". I have been developing client
server application in VC++.NET with SQL Server 2000. I installed my SQL
Server on Windows Server 2003 machine and configured as Domain Controller ex.
soficomdrc.com. The client machine have client utility of SQL Server. How to
solve this problem? Can someone explain to me? why?
My connection string like this:
static String* SQL_CONNECTION_STRING =
S"Server=GSDC01;Database=Soficom;Integrated Security=SSPI";
Thanks in Advance
Jose
There is a lengthy troubleshooting article in the knowledge
base that lists all the steps you need to follow on trying
to track down the issue. Refer to:
How to troubleshoot the "Cannot generate SSPI context" error
message
http://support.microsoft.com/?id=811889
-Sue
On Wed, 5 Oct 2005 08:26:06 -0700, JoseTA
<JoseTA@.discussions.microsoft.com> wrote:

>Hi Everyone,
>I have a problem while executing my application on the network. I don't know
>why this error "cannot generate SSPI context". I have been developing client
>server application in VC++.NET with SQL Server 2000. I installed my SQL
>Server on Windows Server 2003 machine and configured as Domain Controller ex.
>soficomdrc.com. The client machine have client utility of SQL Server. How to
>solve this problem? Can someone explain to me? why?
>
>My connection string like this:
>static String* SQL_CONNECTION_STRING =
>S"Server=GSDC01;Database=Soficom;Integrated Security=SSPI";
>Thanks in Advance
>Jose
|||That I already read. I coudn't understand fully. That's why I cam to forum.
Jose
"Sue Hoegemeier" wrote:

> There is a lengthy troubleshooting article in the knowledge
> base that lists all the steps you need to follow on trying
> to track down the issue. Refer to:
> How to troubleshoot the "Cannot generate SSPI context" error
> message
> http://support.microsoft.com/?id=811889
> -Sue
> On Wed, 5 Oct 2005 08:26:06 -0700, JoseTA
> <JoseTA@.discussions.microsoft.com> wrote:
>
>
|||Is the client machine on the domain? Are you trying to connect with a local
user or a domain user?
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"JoseTA" wrote:
[vbcol=seagreen]
> That I already read. I coudn't understand fully. That's why I cam to forum.
> Jose
> "Sue Hoegemeier" wrote:
|||Yes I am connecting the client machine by domain user. I can access my SQL
Server by Enterprise Manager.
Jose
"MeanOldDBA" wrote:
[vbcol=seagreen]
> Is the client machine on the domain? Are you trying to connect with a local
> user or a domain user?
>
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "JoseTA" wrote:
|||Trying to solve that over the newsgroups isn't an easy task
as there is a lot that goes on with connectivity errors. But
some things to start with...when you can connect using
Enterprise Manager, is that across the network?
Are you trying this (Enterprise Manager and your
application) from different PCs?
Are you trying this with different accounts being that you
are using Windows Authentication?
Go to a PC where your app won't connect and try to ping the
server by server name. If that doesn't work, try to ping the
server by IP. If that doesn't work, you have network issue.
Next, try to connect with your app by logging into the PC
that's running your app using a windows login you know has
connected to SQL Server before. One that has the client
utilities are installed. If your app can't connect, then
trying connecting on this same PC still using Windows
Authentication with Query Analyzer. If that does work, its
something specific to how your app is connecting or the
connection string. Sometime you can connect with named pipes
but not TCP/IP protocol and get this error. In that case,
you can try modifying your connection string to use named
pipes and see if you can connect and get around the error.
If that doesn't work, try creating an alias using the client
network utility on the client that can't connect.
And that's just a few of the things you can start checking
and modifying (one at a time and test after each step) to
see if you can get things going.
-Sue
On Fri, 7 Oct 2005 01:55:08 -0700, JoseTA
<JoseTA@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes I am connecting the client machine by domain user. I can access my SQL
>Server by Enterprise Manager.
>Jose
>"MeanOldDBA" wrote: