Re-edit Import/Export Wizard saved file package (SSIS Newbie)

Question

Re-edit Import/Export Wizard saved file package (SSIS Newbie)

Woody-458823

Mr or Mrs. 500

Points: 515
More actions
October 1, 2009 at 10:18 am

#210362

We have an SSIS package which is causing us a problem by truncating a table which we do not want truncated. However, there are a dozen other tables which get truncated and re-populated which we want to keep. This is a package which has been running for a long time and the current staff is unaware of who created it and how.
I believe it was created with the Import Wizard. The source data comes from another SQL Server database and is copied to existing tables which are truncated for the import.
How can I open the dtsx file to edit it so I can remove a table import or change whether or not it gets truncated. I can open the dtsx in BIDS but that doesn't show me much at all. In the designer, there are no Data Flow components. There are only Control Flow components.
From what I have been able to find out (newbie to SSIS), the components I see are all the result of the Wizard. I have only been able to find 1 of the components which shows any SQL. It contains all the truncate table statements. I would assume the Transfer Task component would have all the information imbedded in it but I don't see how I can expose it either.
How or where can I get to see/edit the table names, mappings, etc. that you would see in the Wizard? I don't see any way in which the Wizard can be used to re-edit a saved file. Am I missing something entirely? Any, and I do mean ANY, help would be appreciated.

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

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

Swarndeep SSCrazy Points: 2347 More actions · Answer 1

Right click on the task, click Edit and Advanced Edit. It will give you options that you may use to fix this.

Just FYI... Using BIDS you can design your package or modify your package and it shows you all what it suppose to do, there is nothing hidden or running in background in SSIS, unless you have any trigger or such object in SQL Server.

Swarndeep

http://talksql.blogspot.com

Woody-458823 Mr or Mrs. 500 Points: 515 More actions · Answer 2

Swarndeep (10/1/2009)
Right click on the task, click Edit and Advanced Edit. It will give you options that you may use to fix this.

I'm assuming you refer to the "Transfer Task". When I right-click and select Edit, I then get an error message

However the properties window doesn't show much more

steveb. SSC-Forever Points: 46733 More actions · Answer 3

The transfer task is created by the wizard and is a complex task that cannot be edited in manually by SSIS, so looking the advanced editor will not work.

You need to have a look at the logic created by the wizard and see if you can easily extract these into SSIS tasks.

adam spencer Hall of Fame Points: 3926 More actions · Answer 4

Normally you can go to edit and see the info. but if you notice in the Properties box the ConnectionSource is another package (inner.dtsx or something like that). So you have parent and child package going on. You will have to find and open that package in BIDS and see the table info and whatever else its doing.

By the way , how did you get the screen shot to show up here in this forum. I would like to do that!

Adam

Woody-458823 Mr or Mrs. 500 Points: 515 More actions · Answer 5

adam spencer (10/2/2009)
Normally you can go to edit and see the info. but if you notice in the Properties box the ConnectionSource is another package (inner.dtsx or something like that). So you have parent and child package going on. You will have to find and open that package in BIDS and see the table info and whatever else its doing.

Thanks much. I did notice that dtsx but did not find it in an obvious location. Guess I'll have to dig deeper.;-)

By the way , how did you get the screen shot to show up here in this forum. I would like to do that!

I use Photobucket.com for online storage of images (free by the way) and it gives you a variety of insertable statements depending on your method of communication. Instead of using the Image icon for a post, I copy the IMG statement from Photobucket.com and paste it into the post.

Woody-458823 Mr or Mrs. 500 Points: 515 More actions · Answer 6

Hmmm....

Turns out the Execute Inner Package Task actually points to a Connection which points to a File in the TEMP directory. I have to assume that the file is being built on-the-fly by SSIS.

I'm beginning to think there really is no way to edit a package created with the Wizard. :w00t:

adam spencer Hall of Fame Points: 3926 More actions · Answer 7

Thanks so much for the image info. I will look into using them too!

This will not help in this case but I have learned how to manual create a package by first seeing how the wizard did it. However, Just last week I thought the wizard was hiding something from me, and it was not. Looking so deeply I missed the big picture. In this case it choose to use to connect to sql server using OLE DB instead SQL server Destination. If it was a grasshopper,it would have jumped on me. Other times It will be the smallest property of a task. So by dragging a task or connection in a blank unaltered package and comparing its properties to the one you need to figure out what and where and how it is doing it, can help.