Post reply

Need to change the databaseName for my DTS package

  • July 29, 2005 at 3:54 am

    #76388

    hi all

    I have a DTS package that has transform data task. Now I want to change the databaseName..(database1 into database2)..I try to create a new database with a different Name..but when I run my package, it says "cannot open database requested in login 'database1'.Login Fails"

    I'm already using a global variable and tried changing it but it still don't works...

    Is it really not possible to change or use it with a different database name?

    Or is there a way?

    Glad to Help,

    Crischell Olegario

  • July 29, 2005 at 7:07 am

    #578162

    Hi

    If you use dynamic properties task and use either global variable or ini files then you can use it on as many databases you like if the structure of the database is the same.

    Hope this helps

    Urvashi

  • July 29, 2005 at 8:03 am

    #578193

    thanks.. but I already tried doing that, it does not work..

    I used a dynamicPropertiesTask and globalVariable..

    how am I gonna use an ini files? maybe, this would work..

    Glad to Help,

    Crischell Olegario

  • July 29, 2005 at 1:58 pm

    #578283

    Do you have any ActiveX scripts in the package that independently attach to the database?

  • August 1, 2005 at 12:39 am

    #578421

    I don't have..

    I only have one package and everything is there..

    I only used transform data task and execute sql query. Then I used some activeX Scripts in transformation.

    I encounter problems with transformDataTask since it always mapped the source and destination column..which would be a problem if I change databaseName.

    Glad to Help,

    Crischell Olegario

  • August 1, 2005 at 4:10 am

    #578454

    Changing the destination database on the connection properties only changes the intial catalog (default). Transformation tasks use the fully qualified table names and you are stuck with database1.dbo.table1 when your destination connection shows "initial catalog=database1".

    So what can you do to duynamically change the destination table to map to database2 ?

    One of my favorate methods is to create a ActiveX script task that loops through all transformation tasks using the VBA.Replace method to change string 'database1' to 'database2' or whatever global varaible you want to use.

    HABIB.

     

    Kindest Regards,

    Habib Zmerli (MVP)

  • August 1, 2005 at 4:33 am

    #578460

    thanks..

    but can you give me some samples...

    Glad to Help,

    Crischell Olegario

  • August 1, 2005 at 10:00 am

    #578592

    Sounds like ur destination is stuck to the old database name...

    usually when you create a new transform data task it sets the destination to something like "database1.dbo.table", so if you are changing the database name, or if you are running this same DTS in different environments, the best solution (I dont wanna say best practice) is to define the destination table without making reference to the database name.

    go into the disconnected edit for the DTS and change the destination table for this task from "database1.dbo.destTable" to "destTable", that'll make your DTS the less tightly coupled to your database names possible.

    hope it helps...

     

    Rayfuss.-

    http://www.e-techcafe.com

  • August 2, 2005 at 12:42 am

    #578730

    thanks "ray L" thats the easiest way to solve it..

    and to all that reply, thanks for the help.

    Glad to Help,

    Crischell Olegario

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply