Showing posts with label task. Show all posts
Showing posts with label task. Show all posts

Monday, March 19, 2012

changeing the table name while using transfer sql server object

Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :

I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks

A link to a similar thread of yours on this topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1094010&SiteID=1|||Thanks Phil for checking.
Although it is similar question but it is pointing to a different functionality of Transfer sql server object task.
I want to be able to copy a table between servers the tricky part is that I don not have the table in the destination and task creates the table in the target server.

so it captures a snapshot of the schema of the source table and applies it to the remote server and the then standard data copy between to tables.

problem: I want to be able to have a different table name for the target database. so I am wondering if it is possible with scripting to do so ( that is fi the task it self has got such a property ) or the task only will create the table with the same name (as the source).

Thanks
|||Just asking and am not trying to take away from your issue, but aren't there better tools for performing schema management rather than using SSIS? I'm not a DBA, so I don't really know the answer to that question, but it seems here that you're stretching the limits of what SSIS was designed to do (even if it *can* do it).

Are you going to run this one time, or many times?|||many times and con-currently
|||Not supported in the Transfer Objects task. However, you could use an Execute SQL after the table is copied to call sp_rename.|||yes, that's how I've done it , but I was wondering if there was a a nyway to stretch the Transfer sql server object task capability.
Cheers

Sunday, February 19, 2012

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave

|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Thursday, February 16, 2012

Change Recovery Model: Without GUI

I need a script that I can change the SQL Server 2000
database from Recovery Model: Full to Simple.
Please help me with this task?
Danalter database FOO set recovery simple
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:17ea901c449a8$fffd24d0$a501280a@.phx.gbl...
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||alter database [your database] set recovery full
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> I need a script that I can change the SQL Server 2000
> database from Recovery Model: Full to Simple.
> Please help me with this task?
> Dan|||Oops, should be set recovery simple
--
Eric Li
SQL DBA
MCDBA
Eric.Li wrote:
> alter database [your database] set recovery full
>