Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looking for a way to move files from a dynamic source location to another dynamic archive location Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 4:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 342, Visits: 986
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
Post #1421539
Posted Tuesday, February 19, 2013 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,100, Visits: 11,900
Loads of examples of this all over the Internet, but try this for starters & see whether it helps.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421615
Posted Tuesday, February 19, 2013 7:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
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
Post #1421645
Posted Wednesday, February 20, 2013 7:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 342, Visits: 986
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.
Post #1422064
Posted Wednesday, February 20, 2013 7:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
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
Post #1422070
Posted Wednesday, February 20, 2013 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 342, Visits: 986
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
[code="other]
@[User::SourcePath] + @[User::MyFileValue]
[/code]
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.
Post #1422094
Posted Wednesday, February 20, 2013 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
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
Post #1422106
Posted Wednesday, February 20, 2013 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1422108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse