Editing a DTS

[StevenK]

We have spent the day (first time for me) setting up DTS's for the importing of text files into SQL Server database tables. This has involved a few false starts, a few near misses and some considerable frustration. Once we have set up the fully functioning DTS we are then able to execute it - having saved it within SQL Server such that it is available for selection in the 'Data Transformation Services' area.
If we have set up, run and had a near miss with a DTS - due to not setting up the column breaks correctly for the text file (i.e. missing a column, or having one too long for instance) - is there a way to open up the DTS and re-edit it such that we do not have to start over again ?
Any pointers would be welcome ?
Similarly is there a way in which we can save the functioning DTS and restore it onto another SQL server ? (should we be saving them as files rather than in the SQL server itself - using the default save option ?)
Thanks in advance.
Steve

 

[tlbroadbent]

Steve,

You can open the DTS package and edit it. To change columns breaks, lengths, etc. double-click on the text file connection. This will open the properties. In the connection properties dialog, click on the Properties button. You'll then be able to make the changes you need.

You can open the Transform Data Task properties and click on the Transformations tab to modify the transformations if necessary.

Check

http://www.sqldts.com/main.asp?nav=1,6,204,0

to learn how to transfer pacakges to another server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

Thanks for the pointer Terry. This knowledge will save us some time now .....
Steve

 

[StevenK]

I've followed the logic of the link provided and have exported (I believe) the latest version of the required DTS's to a text file. Having moved this file to the target machine (SQL servers are not attached) I am now unsure as to how to restore the DTS as exported.
Can you suggest what I need to do for this stage ?
Thanks again.
Steve

 

[tlbroadbent]

You should be able to open the new server in Enterprise manager, Right click Data Transformation Services and select Open Package. You should then be able to browse to the location of the saved file and open it. Then save it on the new server. Make sure you select the SQL Server option for saving. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

I think I'm a little confused with this - sorry.
I've created one DTS (DTS_1) that is used to import a text file to a SQL Server table on SQLServer 1.
I have SQLServer2 at a different location (and not attached in any way to SQLServer1).
I have created a DTS (DTS_2) to export the first DTS (DTS_1 - most recent version of) to a text file. The resultant file is about 200KB+ and contains (seemingly) garbled text.
This text file has now been moved onto the second machine where SQLServer2 runs.
How do I now use this text file to set up DTS_1 on SQLServer2 ??
Sorry if I've missed the obvious.....
Steve

 

[tlbroadbent]

I've never used DTS to export a package. I always open a package and select File | Save As. I then save as a Structured Storage File or a VB File. If you save as a Structured Storage File, it will contain unreadable data. A VB File will contain VB script.

Move the file to server2. Open Enterprise Manager on Server2 and right click Data Transformation Services. Select Open Package from the menu. Then browse to the file and open. It should open in the Package editor. Then save it (File | Save As) to SQL Server rather than to a file. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

OK - I can now move the DTS package from one machine to another. I now experience the problem where I try to do anything with the transferred DTS package on the second machine (not the machine the DTS was created on) I am presented with an error indicating a log-in / password problem . The 2 SQL servers have different passwords. I'm guessing that the log-in/password of the first server (where the DTS package is created and saved from) is embedded in the file transferred disabling me from accessing it on the second server.
Is there a way round this ?
Thanks again.
Steve

 

[spone]

I am doing something similar but I need to use a DTS package to import multiple text files into one table and I am not familiar with the code needed to do that. Can anyone help me? Thanks

 

[tlbroadbent]

spone,

Check these links. In the future, please post new questions in a new thread. Thanks.

http://www.swynk.com/friends/green/dtsdirlooppkg.asp

http://www.swynk.com/friends/green/dtsdirloopstep.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

I'm still facing the issue where moving the DTS package from one server to another still presents me with an error due to the user-name/password.
Am I able to over to over-ride this ?
Or do both servers need to have the same password set-up ?
Thanks again.
Steve

 

[tamathaltarak]

Hi,

I also have the same problem as you StevenK. However I also change the database as well. e.g.
Server1 is my test box and Server2 is my production box.

Server1 = Database1 = User1
I have a dts package here to import all tables from access2k database to Database1.

Server2 = Database2 = User2
I want the same package as Server1 here but need to change the server and database and user.

Have you found a workaround for this yet. If so can you post it for me please

Thanks

Tamath Altarak

 

[SQLSister]

If you open a DTS package in design mode, you can right click on the connections and change the passwords and/or servers used.

What I do to move from one server to the other is use file save as to save the package under a new name (and location if you want) and then I open the new package and change the server connection information.

Hope this helps.