Transfer Logins Task, Can't Set LoginsList Or DatabasesList From Variable

Jan 28, 2008

I need to make a general package to copy SQL databases between servers.
A package with fixed task properties work fine.
But my plan is to make a general package and use varables to handle parameters like connection strings, server names, database names etc.
My problem is the Transfer Logins Task and its collection properties like LoginsList and DatabasesList which uses string collections.
In my understanding I can't make a ordinary variable in "design mode" for string collection. For that reason I wrote some code in a Script Task to set an Object variable to a string collection. And then use the object variable to set the LoginsList or DatabasesList property.

But I get following error:
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at Transfer Logins Task: The data type of variable "User:ourceDB_list_o" is not supported in an expression.
Error at Transfer Logins Task: Reading the variable "User:ourceDB_list_o" failed with error code 0xC00470D0.
Error at Transfer Logins Task: The expression "@[User:ourceDB_list_o]" on property "DatabasesList" cannot be evaluated. Modify the expression to be valid.


My code to set the object variable:

Dim DBs As System.Collections.Specialized.StringCollection = New System.Collections.Specialized.StringCollection()

DBs.Add(Dts.Variables(0).Value.ToString())

Dts.ExecutionValue = DBs


Grateful for any guidance.

Regards, Jonas

View 1 Replies


ADVERTISEMENT

Transfer Logins Task

Feb 27, 2006

Could anybody tell me whetherTransfer logins task transfers passwords for logins or not?

View 1 Replies View Related

Transfer Logins Task Between Servers

Nov 3, 2015

As we all know logins and jobs aren't replicated when using AllwaysOn. What i wanna know is if the "tranfer login taks" will be enough to cover the transfer of logins between servers.

View 7 Replies View Related

Transfer Logins

Jul 12, 2000

HI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..

-thank u.

--Siva

View 2 Replies View Related

Logins Transfer

Sep 18, 2000

Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated

View 1 Replies View Related

Transfer Logins

Nov 15, 1999

I would like to know how I can transfer all Login and password and access records from one SQL Server to another, without transferring the databases. I know I can do this via DTS, but I can not figure out the right combination for bringing over the login names. passwords, and roles, with out transferring the databases. The databases have been transfered already via a backup and restore process. Now I need all of the login infomation from the old server to come across. In DTS I go through the Wizard saying to transfer the Master Database from one server to the other, and transfer objects and Data between SQL Server databases. Under the selcet options to transfer page, I uncheck the "Use Default Options" , and on the options page I select all of the "Security options" and deselect all of the "Table options" (Indexes, triggers, keys, etc). The process runs for a short time before bombing out with a failure. I can not find a log file under the Log folder to tell me where it has failed. Can anyone one lend me a hand with this?

TIA

Ken Merrigan

View 2 Replies View Related

Transfer Logins - Plz Help

Apr 12, 2006

Hi all
In the process of migration of a database from one SQL 2000 server to another, I am transfering the logins using the microsoft documented script

http://support.microsoft.com/kb/246133/

When i am executing the script on the source server, I am getting the following error.

Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.

In all the past migrations , I have used this without any issues, but not sure what have changed.

Or am i doing something really stupid.
Can you guys pls execute this in your server and see whether you are able to create the sp_help_revlogin stored procedure.

Thanks in advance
Sreenath

View 3 Replies View Related

Transfer Logins With Passwords And SQL2005

May 1, 2008

I have versions of sp_hexadecimal and sp_help_revlogin I used with SQL 2000 to transfer logins. Will they work with SQL 2005 as-is? Might I need updated versions?

View 2 Replies View Related

Transfer Logins And Passwords To Another Server

Mar 28, 2006

I€™m using express edition of SQL server 2005. 9.00.1399.06 (Intel X86)
 
I need to transfer all SQL logins and passwords from one server to another.
 
 The script  at http://support.microsoft.com/kb/246133  (designed for sqlserver 7.0 and 2000)  This script references a system table (master..syslogins) that may not exits in sqlserver 2005. I get the following error:
 
EXEC master..sp_help_revlogin
 
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
 
 
Would SMO be something to try for transfering logins and passwords?
 
Thanks,
Karl

View 3 Replies View Related

Transfer Logins From Sql2000 To Sql2005

Sep 20, 2005

Hi,

View 4 Replies View Related

Transfer Logins From SQL Server 2000 To 2005

May 13, 2008

Hi

How to transfer logins from 2000 to 2005?


Raj.

View 1 Replies View Related

Transact SQL :: How To Transfer Logins And Passwords From One Instance To Another

Jun 11, 2015

I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2

View 4 Replies View Related

SSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables

Feb 21, 2008



I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.

The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.

I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)

Any option anyone knows will help.

Thanks.

View 17 Replies View Related

SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction

Feb 1, 2007

In short, does the €śTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €śTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €śThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View 9 Replies View Related

How To Use Transfer SQL Object To Copy An Entire Database Including Logins And Users

Dec 14, 2007



I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.


I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.

The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.


declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor

View 4 Replies View Related

The Task Transfer SQL Server Objects Task Cannot Run On This Edition Of Integration Services. It Requires Higher Level Edition.

Jun 23, 2006

Error code: 0xc0012024

Using "Integration Services Project" template in Business Intelligence Studio. Using platforms Visual Studio 2005 along with SQL Server 2005.

Getting the error while trying to execute package after loading it programmaticaly.

I've just one task "Transfer SQL Server Objects Task" on my Integration Services package. But when I try to execute it from VS 2005 project programmaticaly, it gives the above mentioned error.

The commands I use:

Package pkg = new Package();

pkg = a.LoadPackage(@"C:Documents and SettingsabcMy DocumentsVisual Studio 2005ProjectslSSISSSISPackage.dtsx", null, true);

DTSExecResult dResult = pkg.Execute();

The the error comes like: error: 0xc0012024 The task Transfer SQL Server Objects Task cannot run on this edition of Integration Services. It requires higher level edition.

Please help me.

Thanks in advance,

Bhupesh

View 11 Replies View Related

SSIS Script Task Alters Package Variable, But Variable Does Not Change.

Oct 25, 2006

I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.

I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?

View 1 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

Transferring Logins SSIS Task And DB Mirroring

May 12, 2008



Hi guys,

I want to create an automatic job on SQL 2005 to transfer logins between 2 servers, (Principle and Mirror). If I use the transfer logins task I get the error:


"The database "x" cannot be opened. It is acting as a mirror database."

I guess this is because it is trying to assign the default databases for the logins??

I've also looked at [sp_help_revlogin] procs but that requires manual intervention.

How does everyone else transfer logins in this situation?

Thanks in advance..

View 10 Replies View Related

Transfer Logins: The Source Server Can Not Be The Same As The Destination Server

Nov 30, 2005

I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?

View 1 Replies View Related

Lost SQL Logins, But Still Have DB Logins - Script To Recreate SQL Logins?

Jan 21, 2007

ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.

Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?

I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.

Any assistance would be greatly appreciated.

Thanks,

John

View 3 Replies View Related

Transfer Database Task

Oct 18, 2006

I am trying to run a transfer database task in IS. The tasks fails saying that a defined role already exists in current database. This occurs if I try a destination overwrite to an existing database and even if the database is not currently allocated with destination overwrite = false.

Has anyone experienced these problems. I am just learning IS and have never experienced this type of problem with the old DTS version.

View 2 Replies View Related

What Is The SSIS Transfer Task?

Aug 15, 2006

I have just been using the Import Data wizard in SSMS and when going through the wizard I checked the "Optimize for many tables" checkbox.

The resultant package contains a task called "Transfer Task". I've never heard of this before. Its not listed in the toolbox and its not documented in BOL.

It does some rather strange things as well.

From what I can determine it uses an XML manifest file (stored in c:documents and settings<user>Local settings emp) to construct a SSIS package on the fly which is then executed by an Execute Package Task.
Its SourceDB property is always "smo_Pubs". Its DestinationDB property is always "smo_Pubs_xfred"
There's no UI for it

All very peculiar.



What is it?

Where has it come from?

Did it arrive with SP1 or have I just never noticed it before?

Why is it preferable to a package with lots of data-flows?

Why is it not documented?

When should I use it/not use it?



Questions questions questions...



Thanks

-Jamie

View 11 Replies View Related

Transfer Database Task

Apr 26, 2006



I am attempting to utilize the 'Transfer Database Task' to copy a SQL 2000 database to 2005.
I would like to set up the task as a job to run nightly and update the 2005 database from 2000.

I have the DestinationOverwrite set to true, the Action set to Copy, the Method is DatabaseOnline.

I receive the following message...
ERROR: errorCode=-1073548784 description=Executing the query CREATE ROLE[RSExecRole] failed with the following error: "User, group, or role 'RSExecRole' already exists in the current database..."

If I set overwrite to true...why is this an issue? Suggestions? Comments?

Thank you

View 3 Replies View Related

Transfer Database Task

Nov 16, 2007

Hi, can you not use this task in Microsoft SQL Server Integration Services Designer Version 9.00.3042.00? It keeps giving me an error when I try to copy a DB using this task. Says I cannot use this task in this edition of SSIS. Is there another way to copy a db so that I can schedule it to run or is there a SP I need to download for this to work? I'm using SQL Server Express as well.

thanks,

View 5 Replies View Related

Transfer Object Task Error.

Jul 19, 2007

Hi,

i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB.

i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description.



[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR :
errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is
available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP
Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".



Thanks,

Haroon

View 1 Replies View Related

Transfer Database Task Error

May 11, 2006

hi,

I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error:

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.



Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB?

thanks,

Derek

View 17 Replies View Related

Transfer SQL Server Objects Task: Nothing Happens

Aug 23, 2006

Hello,

I am trying to copy a subset of tables, stored procedures, and views from one database to another database on a named instance of SQL Server 2005. I am attempting to use the Transfer SQL Server Objects task in Integration Services.

I am able to create both Source and Destination connections, and specify the objects I want to transfer. When I run the package, the task turns yellow while it's processing, then green when it completes.

At this point, no objects have been transferred to my destination database. No tables, no procs, no views, nothing. No error has been generated. No information is written to an SSIS log file, other than the fact that objects are being transferred, and then the package is finished.

Does this have something to do with the fact that I'm using a named instance of SQL Server 2005?

Thanks,

Ken

View 7 Replies View Related

Transfer SQL Server Objects Task

Jan 16, 2008

Hi all,


I am using SSIS Transfer SQL Server Objects Task to copy all the objects of a database to another without the data. The properties that i have set for this task is as:


DropObjectsFirst = True
IncludeExtendedProperties = True
CopyData = false
CopySchema = True
UseCollation =True
IncludeDependentObjects = True

CopyAllObject = False
CopyAllTables = True
CopyAllViews = True
CopyAllStoredProcedures = True
CopyAllUserDefinedFunctions = True
CopyAllDefaults = True
CopyAllUserDefinedDataTypes = True
CopyAllPartitionFunctions = True
CopyAllPartitionSchemes = True
CopyAllSchemas = True
CopyAllSqlAssemblies = True
CopyAllUserDefinedAggregates = True
CopyAllUserDefinedTypes = True
CopyAllXMLSchemaCollections = True

CopyIndexes = True
CopyTriggers = True
CopyFullTextIndexes = True
CopyPrimaryKeys = True
CopyForeignKeys = True

All the security options are set to false.

When I execute this task, it fails (with the errorCode=-1073548784) at a point where it tries to create a table which is inside a schema, because the script for creating the table is executed before creating the schema. So, if have a table [Person].[Employee], the script "Create Table [Person].[Employee]" gives an error


The specified schema name "Person" either does not exist or you do not have permission to use it.
It cannot be a permission related issue as am using the sa account to connect to both the source and the destination.
Hence, I conclude that the SSIS task here tries to copy the table first without creating the schema and so the excution fails.

Is this a bug in SSIS? or am missing anything? and what can be the workaround this?

View 3 Replies View Related

Transfer SQL Server Objects Task

Jun 2, 2006

Hi,

When i'm trying to transfer SQL Server Objects from a SQL Server 2000 Database to another i got the following error: [Transfer SQL Server Objects Task] Error: Table "bank" does not exist at the source.

This just appens in some spcific situations:

- When i select the tables i want to transfer, using the option "CopyAllTables" it works fine;

- When i use specific instance as source. Using the Development machine as source it works fine, when i use the machine from Pre-Production (the one i whant to use) it doesn't .

Can anyone tell me why does this appens!?

 

Thanks,

VĂ­tor Ferreira

View 3 Replies View Related

Problem With Transfer Database Task

Oct 18, 2006

Hello everyone,

I am having a problem using the "Transfer Database Task" in SSIS. I want to copy (and overwrite) DestDB with SourceDB on one SQL Server, while SourceDB stays online.

So, I add the task to my control flow and go into the properties. Here's how I have the editor setup:


SourceConnection: MySQL2005Server
DestinationConnection: MySQL2005Server

DestinationDatabaseName: DestDB
DestinationDatabaseFiles: (I update all files to point to the correct destination folder)
DestinationOverwrite: True

Action: Copy
Method: DatabaseOnline
SourceDatabaseName: SourceDB

When I set these options and hit ok, I get validation errors. For every file in the source database, I get an error in the Transfer Database Task, 'Could not find the file "d:\SourceDB_XX" in the source database "SourceDB"'.

Does anyone have any ideas as to why I am getting this or of a way to fix it? Any reason why it puts 2 's in the file path?

Thanks in advance,

Jarret

View 4 Replies View Related

Variable Inside A Variable From Sql TAsk

Sep 28, 2006

I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:

"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""
As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.

[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Where am I wrong?

TIA

View 12 Replies View Related

SQL Variable And IS Variable In Execute SQL Task

Jan 3, 2007

Hi,

 

I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:

 

DECLARE @my_variable int

, <several_others>

SET @my_variable = ?

<do_some_stuff>

 

Of course, I also set up the parameter mapping.

However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.

I have several reasons for wanting to do this:

- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.

- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.

- Passing the variable once also makes it easier to design the script outside IS using Management Studio.

- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.

- I prefer to use the OLE DB connection manager because it's a de facto standard here.

 

Could anyone help me out with the following questions:

- Is the above possible?

- If so, how?

- If not, why not?

- If not, what would be the best way around this problem?

 

Thanx in advance,

Pipo

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved