Migrating DTSX from development server to production.

Question

Migrating DTSX from development server to production.

david.wheelock

Mr or Mrs. 500

Points: 574
More actions
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.

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

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

Kory Becker Hall of Fame Points: 3585 More actions · Answer 2

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

david.wheelock Mr or Mrs. 500 Points: 574 More actions · Answer 3

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://www.sqlservercentral.com/Forums/Topic1034677-391-1.aspx

mgbp SSC Enthusiast Points: 177 More actions · Answer 4

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?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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://www.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

david.wheelock Mr or Mrs. 500 Points: 574 More actions · Answer 6

@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