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.