Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Task Fails - confirmed Connection and SQL is fine. Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 8:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
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.
Post #1423860
Posted Tuesday, February 26, 2013 3:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1423975
Posted Tuesday, February 26, 2013 5:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
Phil, I will Phollow your advice, although I think I did number 1 already...

Thanks
Crusty
Post #1424032
Posted Tuesday, February 26, 2013 6:09 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
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
Post #1424040
Posted Tuesday, February 26, 2013 6:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1424047
Posted Tuesday, February 26, 2013 6:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 PM
Points: 183, Visits: 322
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.
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.
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.
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.....
Post #1424056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse