February 19, 2013 at 4:21 am
I got the following situation:
A database with a settings table and archive tables.
(In the setting table we store the location for both input as archive folders)
We get csv files in the archive folder.
The ssis package we have so far reads in the source folder in a variable from the database.
A for each loop runs through all the csv's in the folder
A section from the filename is extracted using a script since we need to use that section as an indicator when the record was read in or updated.
The data from the csv is read into the archive tables
Now I need to move the files from the source folder to the archive folder.
How do I do this
February 19, 2013 at 6:47 am
Loads of examples of this all over the Internet, but try this for starters & see whether it helps.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 19, 2013 at 7:27 am
Sounds like you already did all the difficult parts of the processing you need to get done. A File System Task should be all you need to move the file to the archive folder.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 7:01 am
OK I found how to do it.
Load the value of the location from the table into a variable.
And then use that variable as an expression for the connectionstring for the flatfile source.
February 20, 2013 at 7:10 am
Nice work. Happy you found a way forward. Thanks for posting back.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 7:48 am
OK so how did I do it, this is a simple step-by-step
1)Load Input folder to variable use execute sql task (User::FileLocation)
2)Load Archive folder to variable use execute sql task (User::ArchiveLocation)
3)Fill a source path variable withe the following expression
@[User::FileLocation] + "\\"
4)
@[User::ArchiveLocation] + "\\"
5)When using 'For each file loop' use script task to get filname
Dts.Variables("FL").Value = System.IO.Path.GetFileName(Dts.Variables("CSVFileName").Value.ToString())
Dts.TaskResult = ScriptResults.Success
User::FL is write variable,while User::CSVFileName is read-only (User::CSVFilename is the variable on which index 0 of the 'For Each File' task is mapped).
6)Fill a variable User::MyFileValue with expression@[User::FL]
7)Fill a full source path variable using the following expression
@[User::SourcePath] + @[User::MyFileValue]
8)Fill a full archive path variable using the following expression
@[User::ArchivePath] + @[User::MyFileValue]
9)Use a file system task 'Rename file' with 'full source path' a source and 'full archive path' as destination
There are definitely corners to cut here but this works.
February 20, 2013 at 8:12 am
Add some screenshots and a sample Package people could download and you've got yourself a nice how-to article for SSC right there...just click the "Write For Us" link on the left nav 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 8:14 am
Thank you very much for posting the detail of your final solution. That'll really help other folks that are looking for a similar solution.
This is also a very common task that people ask a lot of questions about. You might want to consider writing an article on how to do this on this forum. It's a chance to help others in a very public manner that would also look pretty good on future resumes to future employers.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply