Mirroring and Logins

I've created some SSIS packages to move logins from the Primary server to the Mirror server. But I'm having a small glitch. Lets say I have a database dbA that's mirrored. I then add a login of some sort, say userA. userA's default database is dbA. When I add that login to the mirrored server, how do I get the default database to be dbA? Currently I'm getting the following error from Alter Login:

ALTER LOGIN [userA] WITH DEFAULT_DATABASE=[dbA]

Msg 954, Level 14, State 1, Line 1
The database "dbA" cannot be opened. It is acting as a mirror database. I'm currently adding the logins with master as the default and then using Alter to attempt setting it. I can mask the error with a TRY block, but if I fail over it seems I'm going to have an issue. Any thoughts on this would be greatly appreciated.

Thanks,

Larry

I think the mirrored DB is inaccessible (just think of it never existed)

The only way I can think of to change the default DB, is to actually break the mirror (or swap Primary/Mirror role = manual failover)

alter the default databases, then re-establish the mirror (swap the role again = manual failover back to original Primary/Mirror)

|||

Thanks. I guess we'll have to implement a policy that we only add logins after hours or create a window of time for adding logins where we can take the database down a couple of times (to fail over and back). Seems a bit out there for a High Availability solution since it will increase downtime a lot compared to what we have now.

Thanks again,

Larry

sql

Migration SSIS in msdb to other server

I depolyed several package in a server A msdb in some logical folders

E.g. msdb\Task1\package1

msdb\Task2\package2

msdb\Task3\package3

I would like to migrate the package to other server B and keep the logical folders tree.

I know I can reploy the packages in B and move them to the logical folders tree one by one, it is too slow.

Is there any fast method to migrate the package and keeping the tree?

Thanks.

Sure, move the tables directly. Should work.

You can use a package to move rows in the sysdtspackagefolders90 and sysdtspackages90 tables. Move the packagefolders rows first to retain RI.

Be careful that there aren't identically named folders or packages, it could get confusing. Also, do this too much and you'll get duplicate IDs etc. So, proceed with caution!!!

Kirk Haselden
Author "SQL Server Integration Services"

Migration of SQL 7.0 package to SQL 2005

Did you try to migrate this package to SQL 2005. The migration process will
migrate the DTS package into SSIS, and encapsulate all functionality it
can't upgrade into mini DTS 2000 packages which will run as subpackages of
the SSIS Package. You can then at your leisure redesign the mini DTS 2000
packages into your SSIS package.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We are migrating from SQL server 7.0 to SQL server 2005.
> In 7.0 I have made a DTS package that does a complex task on pre-scheduled
> times, started by a SQL job.
> This is a brief description of the complex task:
> -Copy files from several computers in the network to a local "working
> directory"
> -Verify the contents of the files (pre-formatted plain text files)
> -Move files with size or content errors to a different directory
> -Load the files that have no errors into a SQL server table (tblImport)
> -Mark records in tblImport that already exist in tblHistory
> -Gather user information about the files that have been succesfully loaded
> into tblImport
> -Create several reports (plain text files) based on the information in the
> files
> -Print the reports to different printers
> -Add the non-marked records in tblImport to tblHistory
> -Delete the non-marked records in tblImport
> -Send e-mails to several recipients with statistical information about the
> task
> This package is made with a number of activex scripts and SQL tasks.
> Because the server this package is running on now is outdated, I have the
> challenge to make this task work in SQL server 2005.
> The database it is using is already transferred to SQL 2005.
> I have seen that creating packages in 2005 is totally different than
> creating packages in 7.0.
> In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> only
> load a .dtsx file.
> To me it is ok if I have to build the package in 2005 from scratch or
> build
> it in a totally different way, just as long as it is not taking me too
> much
> time. A different way could be that I copy and paste the scripts and SQL
> statements into several steps of a 2005 job, with some minor changes, but
> I'm not sure if that is a good solution. Especially when something goes
> wrong, I would like to know exactly where things went wrong and what steps
> have to be executed to finish the job.
> Any help or ideas are appreciated!
>
>
Can you save the dts packages as structured storage and then open them up in
SQL 2000 Package Designed and save them there and then try to use the
migration wizard?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> Thanks for your response Hilary.
> Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> gives
> me an error saying "This SQL Server version (7.0) is not supported."
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> will
> news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> pre-scheduled
> loaded
> the
> the
> the
> but
> steps
>

Migration of DTS to SSIS

Well..

Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.

Is it possible to edit the SSIS pckage using management studio or integration services?

Please move this question to the " SQL Server Integration Services forum" .

Thanks

Migration of DTS to SSIS

Well..

Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.

Is it possible to edit the SSIS pckage using management studio or integration services?

Please move this question to the " SQL Server Integration Services forum" .

Thanks

Migration of DTS packages to SSIS

I am in a situation where we are redesigning our Datawarehouse. Currently we have our Datawarehouse in SQL 2000 and we are rebuilding from scratch in SQL 2005. This means that even though we will get the same tables but we are planning to rename each & every attribute to make it more meaningful.

The question is like this:

We can migrate the current DTS packages to SSIS but since all the ODBC connections , field names(attributes) will change is it worth it to leverages the DTS packages ?

Also we convert the julian date to gregorian date in our DTS packages but since SSIS has a feature to convert julian date it would be redundant to migrate the packages and my feeling is to create new packages in SSIS and start on a clean slate.

Is there anybody who was in such situation ?

Please advise.

Adarsh Mathur

Personally I would start with a clean slate. I think this will mean you are less likely to use DTS patterns and practices, which are often no longer the most appropriate (assuming they still work), when working in the brave new world of SSIS. Plan your ETL process, define the source to target mappings, and work from that rather using the DTS packages as the template.

Migration in SSIS

Does anyone know about procedure of doing the migration of project in SSIS. We just need to copy the project folder or something else, so that every connection also gets configured on the new server. What is to be done to move the project onto other server?

Which aspect is not covered adequately by Books Online? There is an entire section on "Deploying Integration Services Packages."

-Doug

|||

Amarpreet wrote:

Does anyone know about procedure of doing the migration of project in SSIS. We just need to copy the project folder or something else, so that every connection also gets configured on the new server. What is to be done to move the project onto other server?

Refer to the section(s) in BOL for:

- Creating deployment utility.
- Using DTSInstall.EXE to install packages on another/same server.
- Using DTUtil.EXE for moving packages between machines.
- Using package configurations (specially using environment variable to hold the configuration file path and then using property expressions to dynamically assign connection properties using variables).

Basically, it takes some work.

HTH,
Nitesh|||Thanks Nitesh,

I am very much new to SSIS. If you could explian the last point of yours.
I have created the deployment utility and have copied my deployment folder to the other machine. Then I am using install package utility provided with SSIS. But the error is with the connections (OLE DB and Flat File), which had been created on my machine. How do I dynamically change the server name, username and password. All the required databases and flat files are present on the new server.
If you could provide the steps...
Thanks,|||Amarpreet,
Configurations are the method for doing this in SSIS. Here are some links that may help:

Location independant packages - http://www.windowsitpro.com/Article/ArticleID/47688/47688.html
Easy package configuration - http://www.sqlis.com/?26
Indirect configurations - http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx
Dynamicly changing packages - http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx
Another method is to change such values via the command-line:
Command-line params: http://blogs.conchango.com/jamiethomson/archive/2004/12/13/451.aspx
Configurations or command-line - http://blogs.conchango.com/jamiethomson/archive/2005/05/17/1424.aspx

Regards
Jamie

Migration in SSIS

Does anyone know about procedure of doing the migration of project in SSIS. We just need to copy the project folder or something else, so that every connection also gets configured on the new server. What is to be done to move the project onto other server?

Which aspect is not covered adequately by Books Online? There is an entire section on "Deploying Integration Services Packages."

-Doug

|||

Amarpreet wrote:

Does anyone know about procedure of doing the migration of project in SSIS. We just need to copy the project folder or something else, so that every connection also gets configured on the new server. What is to be done to move the project onto other server?

Refer to the section(s) in BOL for:

- Creating deployment utility.
- Using DTSInstall.EXE to install packages on another/same server.
- Using DTUtil.EXE for moving packages between machines.
- Using package configurations (specially using environment variable to hold the configuration file path and then using property expressions to dynamically assign connection properties using variables).

Basically, it takes some work.

HTH,
Nitesh|||Thanks Nitesh,

I am very much new to SSIS. If you could explian the last point of yours.
I have created the deployment utility and have copied my deployment folder to the other machine. Then I am using install package utility provided with SSIS. But the error is with the connections (OLE DB and Flat File), which had been created on my machine. How do I dynamically change the server name, username and password. All the required databases and flat files are present on the new server.
If you could provide the steps...
Thanks,
|||Amarpreet,
Configurations are the method for doing this in SSIS. Here are some links that may help:

Location independant packages - http://www.windowsitpro.com/Article/ArticleID/47688/47688.html
Easy package configuration - http://www.sqlis.com/?26
Indirect configurations - http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx
Dynamicly changing packages - http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1093.aspx
Another method is to change such values via the command-line:
Command-line params: http://blogs.conchango.com/jamiethomson/archive/2004/12/13/451.aspx
Configurations or command-line - http://blogs.conchango.com/jamiethomson/archive/2005/05/17/1424.aspx

Regards
Jamie

Migration from sybase 8.0 to mssql server 2005

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

Maybe these article will help:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0661.mspx
http://www.microsoft.com/technet/itsolutions/migration/unix/sybsqlsv/default.mspx
http://www.microsoft.com/downloads/details.aspx?familyid=E7541305-891F-494A-B58B-2EED819C4411&displaylang=en
http://www.microsoft.com/sql/prodinfo/compare/sybase.mspx
http://www.devarticles.com/c/a/SQL-Server/Migrating-from-Sybase-to-SQL-Server/|||Basically that artical explaining migration from sybase|unix to sqlserver 2000 But not mssql server 2005 ,i gone through that article before but i did not find any help from that please can you make it clear.

Migration from DTS to SSIS

Hi everybody,

I would REALLY appreciate it if some had a link to a good tutorial about migration from DTS to SSIS, but without visual studio . net.

For example I need to use DTS package in sql server 2005 and execute them, I used migration wizard (SQL 2005) but it didn't work because i have in my computer sql 2000 et sql 2005 and the server name is PC2, however when i use the server source PC2 and the server destination PC2 an error message appear "server destination should be a sql server 2005 instance ".

help me please..

Thanks very much anyone response my question.

Moving to the SSIS 2005 forum from the SSIS 2008 forum.|||

If the connection is to a computer with both 2000 and 2005 on it, the 2005 install needs to be referenced by the server and instance name (MACHINENAME\INSTANCENAME).

Migration from DTS to SSIS

Hi everybody,

I would REALLY appreciate it if some had a link to a good tutorial about migration from DTS to SSIS, but without visual studio . net.

For example I need to use DTS package in sql server 2005 and execute them, I used migration wizard (SQL 2005) but it didn't work because i have in my computer sql 2000 et sql 2005 and the server name is PC2, however when i use the server source PC2 and the server destination PC2 an error message appear "server destination should be a sql server 2005 instance ".

help me please..

Thanks very much anyone response my question.

Moving to the SSIS 2005 forum from the SSIS 2008 forum.|||

If the connection is to a computer with both 2000 and 2005 on it, the 2005 install needs to be referenced by the server and instance name (MACHINENAME\INSTANCENAME).

Migration exceptions

Hi,
How should one deal with exceptions generated by the DTS to SSIS conversion wizard in sql server 2005?
ThanksWhat exceptions are you getting? That would be a good place to start.