July 7, 2012 at 4:29 am
Hi,
I am trying to rename the file in SSIS using File System Task .But in that a file is renamed at the same time it is being moved from Source to destination.
But my requirement is to only rename the file in the existing location.
Example, if I have a file name called text.xlsx at D:\Share Folder.
Then I only want to change the name of the file i.e text_new.xlsx at the sa location.
Then after rename it would be,
D:\Share\text_new.xlsx
Would anyone please help me.
Thanks in Advance.
July 7, 2012 at 9:37 am
I would use two file system tasks or perform the move via a script component.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2012 at 9:41 am
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.
I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 7, 2012 at 3:19 pm
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2012 at 3:26 am
Jeff Moden (7/7/2012)
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
How would you rename a flat file in TSQL? By writing a script? 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 9, 2012 at 10:09 am
Koen Verbeeck (7/9/2012)
Jeff Moden (7/7/2012)
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
How would you rename a flat file in TSQL? By writing a script? 😀
+2:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2012 at 10:25 am
SQLRNNR (7/9/2012)
Koen Verbeeck (7/9/2012)
Jeff Moden (7/7/2012)
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
How would you rename a flat file in TSQL? By writing a script? 😀
+2:-D
xp_cmdshell?
July 9, 2012 at 4:19 pm
Koen Verbeeck (7/9/2012)
Jeff Moden (7/7/2012)
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
How would you rename a flat file in TSQL? By writing a script? 😀
Unfortunately, yes. That's something that I would expect an ETL tool to handle easily but it doesn't seem that SSIS can do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2012 at 4:31 pm
Jeff Moden (7/9/2012)
Koen Verbeeck (7/9/2012)
Jeff Moden (7/7/2012)
Phil Parkin (7/7/2012)
SQLRNNR (7/7/2012)
I would use two file system tasks or perform the move via a script component.I think you might mean a script task, and I agree.
The extra flexibility (and, I think, consistency) afforded by scripting your file rename is worth it.
It still amazes me that SSIS cannot do such simple things without someone having to write a script for it.
How would you rename a flat file in TSQL? By writing a script? 😀
Unfortunately, yes. That's something that I would expect an ETL tool to handle easily but it doesn't seem that SSIS can do that.
SSIS can rename files. I do it all the time.
It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 9, 2012 at 7:52 pm
Koen Verbeeck (7/9/2012)
SSIS can rename files. I do it all the time.It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
OK. I stand corrected. :blush: I've not ever done a deep dive on SSIS. I was listening to some people that I have a little bit less trust in now.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2012 at 6:41 am
Jeff Moden (7/9/2012)
Koen Verbeeck (7/9/2012)
SSIS can rename files. I do it all the time.It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
OK. I stand corrected. :blush: I've not ever done a deep dive on SSIS. I was listening to some people that I have a little bit less trust in now.
Just a quick example to illustrate how easy this is. You don't need to be a hard-core .NET developer to do this stuff.
Here are five lines of code (this was all I needed to add to the standard script task template):
string filePath = @"c:\temp\";
string oldFilename = "SourceData.csv";
string newFilename;
newFilename = oldFilename + "." + DateTime.Now.ToString("yyyy-MM-dd");
System.IO.File.Move(filePath + oldFilename, filePath + newFilename);
The fourth line takes the existing file name (SourceData.csv) and adds a date stamp to it, storing the result in a variable.
Line 5 renames the existing file so that it has the new name. Optionally, it could move the file too (that's what File.Move does). And all in one line of code. Too easy!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 10, 2012 at 8:45 am
Thanks, Phil... I appreciate your time and the example.
Shifting gears a bit, I still think it strange that DBAs will allow such things to go on in SSIS packages but absolutely cringe at the idea of using xp_CmdShell for the same thing. Yeah... I know... they think the security risks are too high. They're probably right because a lot of systems aren't properly locked down to begin with. If they were, there wouldn't be a problem with using xp_CmdShell in code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2012 at 10:18 am
Jeff Moden (7/10/2012)
Thanks, Phil... I appreciate your time and the example.Shifting gears a bit, I still think it strange that DBAs will allow such things to go on in SSIS packages but absolutely cringe at the idea of using xp_CmdShell for the same thing. Yeah... I know... they think the security risks are too high. They're probably right because a lot of systems aren't properly locked down to begin with. If they were, there wouldn't be a problem with using xp_CmdShell in code.
Agreed. ssis but not cmdshell. or even use cmdshell but not clr. If you know what is happening inside the dbms, then document and secure to that end.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 14, 2012 at 6:55 am
SQLRNNR (7/10/2012)
Jeff Moden (7/10/2012)
Thanks, Phil... I appreciate your time and the example.Shifting gears a bit, I still think it strange that DBAs will allow such things to go on in SSIS packages but absolutely cringe at the idea of using xp_CmdShell for the same thing. Yeah... I know... they think the security risks are too high. They're probably right because a lot of systems aren't properly locked down to begin with. If they were, there wouldn't be a problem with using xp_CmdShell in code.
Agreed. ssis but not cmdshell. or even use cmdshell but not clr. If you know what is happening inside the dbms, then document and secure to that end.
Even I don’t prefer to use ‘xp_CmdShell’ but at times when you are forced to do couple of things with t-sql only; I don’t see any other option. But considering the security threat involved in it, we do enable it for a while (couple of milliseconds), execute the command & disable it in sql modules. The sql modules are either signed with certificates or executes in super users context thus bit safe.
July 14, 2012 at 9:17 am
Dev (7/14/2012)
But [font="Arial Black"]considering the security threat involved[/font] in it, we do enable it for a while (couple of milliseconds), execute the command & disable it in sql modules. The sql modules are either signed with certificates or executes in super users context thus bit safe.
See? That's the whole stigma of xp_CmdShell. What security threat? "Everyone" says it's a security threat but, if your system is properly locked down, then it's actually NOT a security threat. If your system isn't properly locked down, then the use of xp_Cmdshell pales in comparison to the other things you really should be worried about.
Part of the stigma that xp_Cmdshell is a threat is because people actually make the huge mistake of giving low prived users the ability to directly run and do whatever they want with xp_CmdShell. To me, that's just insane. In fact and to me, having any non DBA users with any more than just PUBLIC privs or PUBLIC with "DataReader" privs is insane. In most cases, even the "DataReader" priv isn't necessary at all. That includes so called "super users". Unless they need DBO privs to do their job (and they usually don't), there's just no reason for it. In any case, they should never have SA privs and they certainly should NOT be given the priv to run xp_CmdShell directly. The SA level of privilege should be reserved for production DBAs and then only when they actually need it.
Although the use of signed certificates is one way to go, if your system is actually and correctly locked down, there usually isn't a reason to bother with signed certificates either even when someone needs to run a stored proc that contains a call to something like xp_CmdShell. In a properly locked down system, users will only have PUBLIC privs and they'll belong to Database Roles that give them EXECUTE privs on only those stored procedures that they need to execute to get their jobs done.
The only time that xp_CmdShell is a threat is when you give people the privs to use it directly and that's easy to avoid even though it's used in stored procedures that those users can execute.
Yeah, yeah... there will be those that say that someone could make a mistake in the future and give a non DBA user elevated privs or the privs to run xp_CmdShell directly and that would open a security gap because of xp_CmdShell. If that happens, then you don't have a properly locked down system anymore. People explain that away by saying "it's possible to hire people who don't actually know what they're doing". Yes it is if you don't know how to hire people that do. And, if you do create such a security gap, you have a whole lot more to worry about than just xp_CmdShell. If you're publicly traded and you get hacked well enough for someone to use xp_CmdShell, then you'll need to explain to the SEC and a couple of other agencies how you let someone hack you at the SA level. And, consider this... if you get hacked, it'll likely be at the SA level and someone who does that can turn xp_CmdShell on just like you.
I've even seen people who rename the xp_CmdShell related DLL to make it supposedly "impossible" to use it even if they do get hacked at the SA level. Guess what? That's NOT going to stop them from getting to the command line. There IS a super simple hack to easily get to the command line through OPENROWSET if you can get into a system at the SA level.
Stop worrying about xp_CmdShell and start worrying about properly locking your system down. Start by making all non DBA users and Logins have only PUBLIC privs with EXECUTE privs on only the stored procs they need to run.
Security is a full time and very necessary job. Just do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply