May 7, 2021 at 2:58 pm
I am trying to add a File System Task to an existing package to move csv files from one folder to another on my local machine. I already have Connection Managers setup for both folders. When I try to configure the DestinationConnection and SourceConnection it tells me that "Type of connection" MyFolderName (i.e my source) "is not a file". Thanks for any clues!
May 7, 2021 at 3:12 pm
You need to have a full file path for both.
Note: When you do a move you can also do a rename at the same time.
Neither connection can be a directory, they both need to point to a specific file.
Normally I use a Script Task to do this stuff as I really don't like the File System Task, just my preference, but if you're comfortable with C# it's another option. It's nice because C# offers built in functions to work with file name and path as separate properties so you don't need to worry about backslashes, not to mention all the other stuff you can do to manage file system stuff.
May 7, 2021 at 3:45 pm
I like the idea of using C# but know very little of the language.... so I want to move the entire contents (several .csv files) of one folder into another... were you thinking something like:
public void Main()
{
string rootFolderPath = @"C:\Users\me\desktop\convo\";
string destinationPath = @"C:\Users\me\desktop\exceptions\";
foreach (string file in rootFolderPath)
{
string fileToMove = rootFolderPath;
string moveTo = destinationPath;
File.Move(fileToMove, moveTo);
}
May 7, 2021 at 3:55 pm
Why not just use a single DOS "MOVE" command instead of reinventing the wheel?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2021 at 4:13 pm
And put the DOS "Move" command into the script above... I'm not following that.
May 7, 2021 at 4:52 pm
I was able to get this to work:
using System.IO;
namespace ST_adc7961e6d564b959438804fe590a5a4
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string sourcePath = @"C:\Users\me\desktop\convo\";
string destinationPath = @"C:\Users\me\desktop\exceptions\";
foreach (string sourceFile in Directory.GetFiles(sourcePath, "*.csv"))
{
string fileName = Path.GetFileName(sourceFile);
string destinationFile = Path.Combine(destinationPath, fileName);
File.Move(sourceFile, destinationFile);
}
}
May 7, 2021 at 5:40 pm
A recent script that I used to move and rename. This uses a local variable that was set from a for each file loop and the Archive dir which comes from a Project parm.
You could also grab the file path from the DTS.Connections["connName"].connectionstring or something like that (don't know the exact syntax off the top of my head).
//include namespace
Using system.IO;
public void Main()
{
// Rename file and move to archive folder.
//User::SrcFilePath,$Project::ArchiveFolder
var newPath = Path.Combine(Dts.Variables["$Project::ArchiveFolder"].Value.ToString()
,"File_ProcessedOn_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt");
FileInfo MyFileInfo = new FileInfo(Dts.Variables["User::SrcFilePath"].Value.ToString());
MyFileInfo.MoveTo(newPath);
Dts.TaskResult = (int)ScriptResults.Success;
}
May 7, 2021 at 5:46 pm
I was able to get this to work:
using System.IO;
namespace ST_adc7961e6d564b959438804fe590a5a4
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string sourcePath = @"C:\Users\me\desktop\convo\";
string destinationPath = @"C:\Users\me\desktop\exceptions\";
foreach (string sourceFile in Directory.GetFiles(sourcePath, "*.csv"))
{
string fileName = Path.GetFileName(sourceFile);
string destinationFile = Path.Combine(destinationPath, fileName);
File.Move(sourceFile, destinationFile);
}
}
yeah that works, just make sure you replace the hard coded stuff with variables/params or you'll be modifying it later.
May 7, 2021 at 5:54 pm
I would also change the process from .Move to a .Copy and a separate delete operation. The reason for that is simple - in Windows, when moving files the file properties are moved as well and if the destination is set to compress the contents the move does not inherit that property. But - when copying a file to a compressed folder the new file will be compressed.
You also need to get the contents of the folder - here is some example code:
bool fireAgain = true;
// Get the root and archive directories
string rootDirectory = Dts.Variables["$Project::RootDirectory"].Value.ToString();
string archiveDirectory = Dts.Variables["User::ArchiveDirectory"].Value.ToString();
// Get our file pattern search criteria
string filePattern = Dts.Variables["$Package::FilePattern"].Value.ToString();
// Get the files from the root directory that match our file pattern
DirectoryInfo info = new DirectoryInfo(rootDirectory);
FileInfo[] files = info.GetFiles(filePattern).ToArray();
// Loop through each file found
foreach (FileInfo file in files)
{
// Set the archive file name
string archiveFileName = Path.Combine(archiveDirectory, file.Name);
try
{
file.CopyTo(archiveFileName);
file.Delete();
Dts.Events.FireInformation(0, "Archive File Task", file.Name + " archived to " + archiveFileName, string.Empty, 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Archive File Task", ex.ToString(), string.Empty, 0);
}
}
With this - set the project parameter rootDirectory to the source, set a package variable for the archive directory and a package parameter for the file pattern and pass those as read-only to the script. This allows for different destinations and file patterns for each package in a project.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2021 at 6:14 pm
On the subject of compressed file destinations, true dat.
But, so far, everyone appears to be writing C as a replacement for simple CLI commands that already exist... it's kind of like having RBAR outside of SQL Server. I'll never understand why people do that or PowerShell to do such things.
Whatever... have fun folks. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2021 at 7:20 pm
Jeff Moden wrote:Why not just use a single DOS "MOVE" command instead of reinventing the wheel?
Jeff where are putting this command? In an Exec Process task or cmd shell in a script?
A part of the reason why people are using the stuff that they're posting is because they're doing it from SSIS. I avoid SSIS like the plague and do things like this quite easily from T-SQL using xp_CmdShell. That's the rub though... there are a load of old wives tales about what a security risk it is for two reasons... they don't know how to configure it correctly and they don't know how to use it correctly even when they configure it correctly. It hasn't been a security risk since at least 2005 WHEN both of those items are true. I was also using it prior to 2005 with the same no- risk a long time before 2005.
If you have to do it in SSIS, you need (and forgive me if butcher the name because I avoid SSIS) a cmd exec task. The problem is that it's difficult to pass parameters to such a task and it's (apparently but only because people seem to avoiding it) difficult to get a processable return from the task.
Those problems aren't really problems when using T-SQL with calls to xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2021 at 7:51 pm
If you have to do it in SSIS, you need (and forgive me if butcher the name because I avoid SSIS) a cmd exec task. The problem is that it's difficult to pass parameters to such a task and it's (apparently but only because people seem to avoiding it) difficult to get a processable return from the task.
The task in SSIS is an Execute Process Task - and you are correct, it is much harder passing data to or returning data from that task. The script task is much easier to pass data in/out and gives you full access to the .NET library.
With that said - I would not use SSIS only for moving files. I use SSIS to import/export files from SQL Server - and then do something else with the files. For example, we need to sFTP a file to one of our vendors - the process will be:
Now - if the only thing needing to be done is to move files, I would use Powershell - because it gives me added abilities that are easier to implement than trying to do that in a DOS batch file. But that is just my preference - and using a DOS batch file for that is just as valid as a PS script.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2021 at 8:02 pm
One more thing - there are a few things that can be done much more easily/better using a script task than the execute process task or file system task or some of the other tasks. Script tasks can be done in either VB.NET or C# - which gives you access to the .NET libraries.
For example, instead of using an Execute Process Task to call out to PKZIP (as an example) - .NET now has built in code to archive/unarchive files. And a lot of the utilities now have .NET components available that can be included and called - which gives you much more flexibility than the old command line functionality.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2021 at 10:38 pm
Jeff Moden
Ok, that makes sense from the TSQL side, the file operations we do are secondary to the primary process and since we already have all the variables and such inside the package the script makes it easy and as Jeffery Williams said doing it with an Exec process task would be more of a pain.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply