February 25, 2013 at 8:16 pm
I have a DTSX package that is a copy of another one. There are a lot of import steps so I thought I would use the original as a template as I'm going to have to do this multiple times. The first step is to truncate the table the text file will be imported to. I use an SQL Task for this with a connection string that I've confirmed is working via clicking the "test connection" button. Also, since I can see the db I'm connecting to, I get a warm fuzzy that the connection is ok. It's a different database, but the table name is the same, plus I'm using connection strings that are already built.
I always test my SQL in SSMS first to avoid typos, etc. I cut and paste into the SQL Statement line in the SQL Task Properties. The statement is a truncate table statement; however, nothing is working so I think that's irrelevant.
When I test the container, it fails and indicates that the Connection was either not set up properly or I don't have permission; however, I'm connecting as the administrator. Here's where it gets weird. If I create a new ssis pkg and create the same step using the same code and the same connection, no changes, it works fine.
Is this a meta-data issue? I'm going to have to build about 8 packages doing almost the same thing and there are about 100 steps to take. I really don't want to rebuild each one from scratch.
Any help is greatly appreciated.
Thanks
Crusty.
February 26, 2013 at 3:40 am
When you copy a package, there are a couple of things you should do which may help minimise meta data issues:
1) Use File/Save As from within BIDS rather than doing a straight file copy.
2) Select the option to generate a new package ID in the newly created package.
Perhaps you could try the above - if you did not already - and see whether things work better.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 26, 2013 at 5:51 am
Phil, I will Phollow your advice, although I think I did number 1 already...
Thanks
Crusty
February 26, 2013 at 6:09 am
Phil,
Here's what I got:
[Execute SQL Task] Error: Failed to acquire connection "192.168.250.199.DimmitTx.CHDRemote". Connection may not be configured correctly or you may not have the right permissions on this connection.
Before I ran the container, I checked the connection at it succeeded. I looked at the properties of the one that works (brand new package) and the one(s) that don't work and other than the ID, they are set up the same. Not sure what else to do. Of note though, the package I'm using as the template, it had a problem with calling things from a new connection. I'm not sure what happened but I wasn't able to use any new connections and had to take other measures. Maybe they're is something wrong with the "Parent" package which is finding it's way into any others I spin off....
Crusty
February 26, 2013 at 6:23 am
What version of SSIS are you using?
I sympathise. Can you confirm that you have got to the point where:
a) Package 1 works.
b) Take a copy of package 1 (as per my first post) and save as package 2. No other changes are made to package 2.
c) Package 2 fails.
If so, this is surely a meta-data issue. Other than firing up an XML editor or text -file differencer and putting your Sherlock Holmes hat on, I'm not sure what to do next. Actually, considering upgrading to 2012 would be good - there seem to be fewer problems like this with it 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 26, 2013 at 6:35 am
Phil Parkin (2/26/2013)
What version of SSIS are you using?I THINK the answer is 2005, or Visual Studio 2005, pkgs save as DTSX
I sympathise. Can you confirm that you have got to the point where:
a) Package 1 works.
:w00t: Works well but when I tried to create a new connection and use it, that part died. The solution was to create a second DTSX package that called the connection I needed. That worked. Hmmmm... I see a pattern here.
b) Take a copy of package 1 (as per my first post) and save as package 2. No other changes are made to package 2.
:crazy: Correct. Save As, no other changes made other than the new connections. There's a bunch of containers and the only real change is the connection.
c) Package 2 fails.
:sick: It dies kicking and screaming, code spraying everywhere.
If so, this is surely a meta-data issue. Other than firing up an XML editor or text -file differencer and putting your Sherlock Holmes hat on, I'm not sure what to do next. Actually, considering upgrading to 2012 would be good - there seem to be fewer problems like this with it 🙂
➡ I'm rather new to this shop and recently finished a push to upgrading our main VM's to 2008, they're a little behind. Then again, I was at Deloitte for a while and even that multi-billion dollar company only recently started rolling to 2012.
I'm thinking something went wrong in the original package that only affects the creation of new connections. Kind of Weird....
Something else... I cut and pasted the containers I wanted into the new package, reset their connections (just a couple of them) and it worked. Something with the package itself.....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply