| Author |
 Topic  |
|
|
starson45
Starting Member
5 Posts |
 Posted - 06/20/2012 : 09:01:43
|
"FINDSTRING( @[User::strTableNameSolarUKbe] , 'DontTruncate_', 1) >= 1? : TRUNCATE TABLE + @[User::strTableNameSolarUKbe] "
I've got a For Each Loop Container with this Execute SQL Statement inside it. All I wanna do (ha yeah!) is to truncate all tables in the database where the name does not contain 'DontTruncate_'
The error I'm getting is vague
"Syntax error, permission violation, or other nonspecific..." whatnot
If table name doesn't contain 'DontTruncate' in name, then don't do anything, simply move on to next table.
Any ideas.
Thank you.
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/20/2012 : 10:34:30
|
how are you running the SSIS package, under what permissions?
Permissions for TRUNCATE TABLE: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
Edited by - yosiasz on 06/20/2012 10:35:13 |
 |
|
|
starson45
Starting Member
5 Posts |
Posted - 06/20/2012 : 11:07:31
|
I'm running the package from within Visual Studio. How do I work out what permissions it's using?
I got the truncate statement to work on a different database that is a copy of the one I'm trying to use now. In that case though, the truncate statement is simply "TRUNCATE TABLE " + @[variableName]
|
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/20/2012 : 11:14:30
|
what type of permissions do you have on the copy database vs the one you are trying to use?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
starson45
Starting Member
5 Posts |
Posted - 06/20/2012 : 11:30:56
|
| AS far as I can see I've got dbo access. So that should be full permissions surely. |
|
|
|
starson45
Starting Member
5 Posts |
Posted - 06/20/2012 : 11:33:37
|
| db_owner |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 06/20/2012 : 11:42:54
|
spin up sql profiler in the back ground and let it trace, run the ssis package and see what the values are for all your variables
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
| |
Topic |
|
ExecuteSQLTask ExpressionBuilder syntax check
