Post reply

Migrating DTSX from development server to production.

  • December 10, 2010 at 2:32 pm

    #230424

    I created a DTSX file on my SQL Server 2008 development server using the SQL Server Import and Export Wizard to import a text file to a table. It works fine and I have it set up to run automatically using SQL Server Agent on the dev-box.

    Fine so far.

    Now I want to move it to the production server but it doesn't work there. The production server complains of "validation error". I think this has to do with the server name and username/password that are apparently stored in the DTSX and are incorrect for the new server. I tried editing the DTSX manually, changing the things I could see, but it still failed, presumably because the encrypted password is wrong.

    My research shows I should use BIDS, and says that I must import the file to BIDS and modify it there and then export it back out to a new DTSX, or perhaps store it in the database. There's also something called SSIS Designer. Should I use that instead? Is it the same thing?

    Can someone please give me a shortest path solution for this?

    Thanks.

  • December 11, 2010 at 5:46 pm

    #1261360

    Hi David,

    Uisng Export\Import Wizard, creates an SSIS package. As it was created for the Dev box, it works fine with those dev box credentials. But it may fail for the prod box due to the differening prod box credentials.

    So you need to open the package in BIDS and edit the coinnection manager credentials by giving the prod credentials instead of the Dev credentials. Actually BIDS and SSIS designer are one and the same.

    So create a New Integration Services Project --- Edit the package connection with Prod creadentials -- Test the package and deploy the package to the Prod server.

    Use Encrypt Sensitive with Password protection level.

    Check this link..

    http://www.protalk.in/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/

    Thank You,

    Best Regards,

    SQLBuddy

  • December 11, 2010 at 7:13 pm

    #1261366

    You may also want to add a config file for the packages that can be updated appropriatley for each environment.

  • December 14, 2010 at 1:15 pm

    #1262445

    Thanks!

    I've got it working as a DTSX and imported to the SQL DB and it works there, too.

    However, now I've encountered a problem getting the package to run in a SQL Agent Job.

    I've created a new topic for that here:

    http://qa.sqlservercentral.com/Forums/Topic1034677-391-1.aspx

  • April 21, 2011 at 1:37 am

    #1314345

    Hi,

    Please help me with the scenarios below:

    A-Server contains:

    1. SSIS Package (dtsx)

    2. textfiles to be imported in Database (of B-Server).

    3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)

    B-Server

    1. SQL Server -engine (with target database).

    2. WITHOUT SSIS Component installed.

    --------------------------------------

    I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.

    This dtsx will import/convert the textfile to the database located in B-Server.

    Both servers are in 64-bit.

    Is it possible with the given situation above? What are the other requirements/components needed?

  • April 21, 2011 at 1:38 am

    #1314348

    mgbp (4/21/2011)

    Hi,

    Please help me with the scenarios below:

    A-Server contains:

    1. SSIS Package (dtsx)

    2. textfiles to be imported in Database (of B-Server).

    3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)

    B-Server

    1. SQL Server -engine (with target database).

    2. WITHOUT SSIS Component installed.

    --------------------------------------

    I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.

    This dtsx will import/convert the textfile to the database located in B-Server.

    Both servers are in 64-bit.

    Is it possible with the given situation above? What are the other requirements/components needed?

    Same remark as in this topic:

    http://qa.sqlservercentral.com/Forums/Topic865414-148-2.aspx

    Crossposting shatters replies and wastes peoples time.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 21, 2011 at 7:17 am

    #1314485

    @mgbp

    I concur with KV. This is really a different question than the one I started this thread with. I think you're more likely to get a knowledgable reponse if you start a brand new thread with the question above. Then come back here and point to the new thread.

    Regarding your question, I really don't have enough info to answer your actual question. Looks like it might work. I'd say give it a try and post your thread if/when you encounter a problem. Be sure to post any error messages, etc.

    Good luck with your question.

    David

Viewing 7 posts - 1 through 6 (of 6 total)

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