can I export tables so that existing tables in destination database will be modified?

I'm working on an ASP.Net project where I want to test code on a local
machine using a local database as a back-end, and then export it to
the production machine where it uses the hosting provider's SQL Server
database on the back-end. Is there a way to export tables from one
SQL Server database to another in such a way that if a table already
exists in the destination database, it will be updated to reflect the
changes to the local table, without existing data in the destination
table being lost? e.g. suppose I change some tables in my local
database by adding new fields. Can I "export" these changes to the
destination database so that the new fields will be added to the
destination tables (and filled in with default values), without losing
data in the destination tables?

If I run the DTS Import/Export Wizard that comes with SQL Server and
choose "Copy table(s) and view(s) from the source database" and choose
the tables I want to copy, there is apparently no option *not* to copy
the data, and since I don't want to copy the data, that choice doesn't
work. If instead of "Copy table(s) and view(s) from the source
database", I choose "Copy objects and data between SQL Server
databases", then on the following options I can uncheck the "Copy
Data" box to prevent data being copied. But for the "Create
Destination Objects" choices, I have to uncheck "Drop destination
objects first" since I don't want to lose the existing data. But when
I uncheck that and try to do the copy, I get collisions between the
properties of the local table and the existing destination table,
e.g.:
"Table 'wbuser' already has a primary key defined on it."

Is there no way to do what I want using the DTS Import/Export Wizard?
Can it be done some other way?

-Bennett"Bennett Haselton" <bennett@.peacefire.org> wrote in message
news:e614455c.0411160123.294dfd10@.posting.google.c om...
> I'm working on an ASP.Net project where I want to test code on a local
> machine using a local database as a back-end, and then export it to
> the production machine where it uses the hosting provider's SQL Server
> database on the back-end. Is there a way to export tables from one
> SQL Server database to another in such a way that if a table already
> exists in the destination database, it will be updated to reflect the
> changes to the local table, without existing data in the destination
> table being lost? e.g. suppose I change some tables in my local
> database by adding new fields. Can I "export" these changes to the
> destination database so that the new fields will be added to the
> destination tables (and filled in with default values), without losing
> data in the destination tables?

In a word, no.

I'd suggest take the same version controlled scripts (you are of course
using version control, right? :-) you used to make the changes in Dev and
run those against the Prod server.

> If I run the DTS Import/Export Wizard that comes with SQL Server and
> choose "Copy table(s) and view(s) from the source database" and choose
> the tables I want to copy, there is apparently no option *not* to copy
> the data, and since I don't want to copy the data, that choice doesn't
> work. If instead of "Copy table(s) and view(s) from the source
> database", I choose "Copy objects and data between SQL Server
> databases", then on the following options I can uncheck the "Copy
> Data" box to prevent data being copied. But for the "Create
> Destination Objects" choices, I have to uncheck "Drop destination
> objects first" since I don't want to lose the existing data. But when
> I uncheck that and try to do the copy, I get collisions between the
> properties of the local table and the existing destination table,
> e.g.:
> "Table 'wbuser' already has a primary key defined on it."
> Is there no way to do what I want using the DTS Import/Export Wizard?
> Can it be done some other way?
> -Bennett|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Mnmd.16$1u.15@.twister.nyroc.rr.com>...
> "Bennett Haselton" <bennett@.peacefire.org> wrote in message
> news:e614455c.0411160123.294dfd10@.posting.google.c om...
> > I'm working on an ASP.Net project where I want to test code on a local
> > machine using a local database as a back-end, and then export it to
> > the production machine where it uses the hosting provider's SQL Server
> > database on the back-end. Is there a way to export tables from one
> > SQL Server database to another in such a way that if a table already
> > exists in the destination database, it will be updated to reflect the
> > changes to the local table, without existing data in the destination
> > table being lost? e.g. suppose I change some tables in my local
> > database by adding new fields. Can I "export" these changes to the
> > destination database so that the new fields will be added to the
> > destination tables (and filled in with default values), without losing
> > data in the destination tables?
> In a word, no.
> I'd suggest take the same version controlled scripts (you are of course
> using version control, right? :-) you used to make the changes in Dev and
> run those against the Prod server.

Uh, no... my "script" is that I move the mouse pointer over the
"wbuser" table in Server Explorer in VS .Net, right-click, pick
"Design Table", and add columns that way :)

So I guess I'll just have to remember to run that "script" against the
Prod database whenever I make any changes.

Still, I would have thought that what I was trying, would be a pretty
common thing for people to want to do... surprised it's not built-in.

-Bennett|||Bennett Haselton (bennett@.peacefire.org) writes:
> Uh, no... my "script" is that I move the mouse pointer over the
> "wbuser" table in Server Explorer in VS .Net, right-click, pick
> "Design Table", and add columns that way :)
> So I guess I'll just have to remember to run that "script" against the
> Prod database whenever I make any changes.
> Still, I would have thought that what I was trying, would be a pretty
> common thing for people to want to do... surprised it's not built-in.

There are third-party tools. Red Gate's SQL Compare is popular.

One reason that this is not built-in is that changes to tables is far
from always something that can be performed automatically. In our shop
we keep all table definitions under source control. To generate an update
script we have a tool that reads SourceSafe between two labels to load
all new and changed objects. For changed tables you get a script that
copies data from the old definition to the new, but this script is really
only a template. In many cases I need to change the script, because a
new column is supposed be initiated with some values derived from
other columns or tables. And there might be even wilder changes.

Point and click may be convenient, but for something which is to be
run several times needs to be automized to be sure that all are updates
are carried out the same way. Thus, you need a script.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can I add a record number as data passes through

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

Looks possible to me using a custom script component to compare incoming fields to the last set coming in. Would work nicely if the data is sorted.|||

bobbins wrote:

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

The Rank transformation will do this for you:

Rank Transform
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

-Jamie

|||

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

|||

mike.groh wrote:

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

Mike,

Its a pleasure. I've been after that sort of feedback for a long time :)

Again I have to reiterate Darren Green's contribution. It was my idea but he realised the idea.

-Jamie

|||

Thanks for the info, I haven't got round to doing this yet but I'm sure it'll work.

Thanks very much, top people!

|||

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

|||

bobbins wrote:

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

The instructions here: http://msdn2.microsoft.com/fr-fr/library/ms136125.aspx are for custom tasks but its intuitively similar for custom components. Look for the section entitled "How to use the Task in SSIS Designer"

-Jamie

|||If you just want the row number function, then use the Row Number Tx http://www.sqlis.com/default.aspx?93, as it does not require a sorted input.|||

I still can't do it because when I select 'Choose toolbox items' in the data flow designer it just list what's there already, I only have an option to browse on the COM and .NET components, is this where I should be putting it?

Thanks.

|||

You are looking at the Data Flow page in the Choose Toolbox Items?

You have scolled down to find the transform, in alphabetical order?

Double check this, and perhaps compare with some screenshots here-

Konesans - Frequently Asked Questions - How do I install a task or transform component?
(http://www.konesans.com/faq.aspx#installtask)

If this does not help, on the machine you are running Visual Studio, go to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents, and look for the DLL file. I'm not sure which transform you are trying right now, so I cannot specify the filename for you here. If the file is not there, then it will never show up in Choose Toolbox Items, so did the install fail?

|||

Yes I am.

Yes I have, it's definately not there. I have checked against the link you provided and I cannot see the component in the list.

I am attempting to use the RankTransform component. I think it installed ok as there were no errors. I've searched for the dll ( it's full name is Conchango.SQLServer.SSIS.DataFlow.RankTransform.dll ) found that it is located in C:\Program Files\Microsof SQL Server\90\DTS\CompFldr so this looks like conformation that it has installed ok.

I've copied it to the directory that you specified and opened the 'choose items' again and it was listed, so I've added it.

I'd like to say a big thanks to yourself and Jamie for the help with this, as someone who doesn't know much about Visual Studio and dll's and stuff you've been an massive help.

I'll now try to use it (I'll have to go back to the instructions!) and let you know how it goes.

Thanks again

|||

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

|||

bobbins wrote:

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

Bobbins,

Thankyou for making me aware of this. it looks as though the partition functionality might not be working. I'll check it out.

-Jamie

|||

Had any luck with it?

Cheers

Can a Data Flow Task mimic Bulk Insert?

Hi Guys,

The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.

Thanks!

Kervy

What destination object are you using? The OLEDB Destination and the SQL Server destination both enable bulk insert options for SQL Server.

How many rows are you inserting?

Donald

|||

Oh you mean to say, by default, an OLEDB source inserts to an OLEDB Destination in bulk? We are only inserting a couple of hundred thousand rows, but the source table is in the US. destinations are in Europe and Asia Pacific, so we need to optimize this as much as we can given the proximity of the servers.

We are using OLEDB source and OLEDB Destination

Thanks

|||

If you need to performance tune your data-flow then you should read a whitepaper that I have mentioned here: http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

There's also more useful information here: http://blogs.conchango.com/jamiethomson/archive/2005/04/20/1319.aspx

Note that SSIS does not insert records one at a time - that would be a performance killer. The SSIS pipeline processes data in buffers and the contents of each buffer is inserted at the same time! A large part of performance tuning your application is finding the optimum buffer size.

Note that in your scenario the bottleneck is likely to be the georgraphic disparity of the source and destination, not how fast SSIS can process it. Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require.

-Jamie

|||

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

|||

KervyChoa wrote:

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

Exactly, hence my previous comment "Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require."

The point here is to reduce the amount of data coming over the wire because that is quite obviously the bottleneck in your scenario.

-Jamie

|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?|||

KervyChoa wrote:

I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?

As we have already determined (twice) the bottleneck is not within the SSIS pipeline so looking at this perf counter is not going to help you.

Unless you pick one of the machines up and move it to a different continent, or improve the speed of the link between them, you're not going to see any eprformance improvement.

-Jamie