SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Task Fails - confirmed Connection and SQL is fine.


SQL Task Fails - confirmed Connection and SQL is fine.

Author
Message
CptCrusty1
CptCrusty1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1304 Visits: 387
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109460 Visits: 22278
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
CptCrusty1
CptCrusty1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1304 Visits: 387
Phil, I will Phollow your advice, although I think I did number 1 already...

Thanks
Crusty
CptCrusty1
CptCrusty1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1304 Visits: 387
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109460 Visits: 22278
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
CptCrusty1
CptCrusty1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1304 Visits: 387
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 :-)

Arrow 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.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search