Looking for a way to move files from a dynamic source location to another dynamic archive location

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Looking for a way to move files from a...

Looking for a way to move files from a dynamic source location to another dynamic archive location

Rate Topic

Display Mode

Topic Options

Author

Message

Resender

Posted Tuesday, February 19, 2013 4:21 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:55 AM
Points: 289, Visits: 683

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

Phil Parkin

Posted Tuesday, February 19, 2013 6:47 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1421615

opc.three

Posted Tuesday, February 19, 2013 7:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421645

Resender

Posted Wednesday, February 20, 2013 7:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:55 AM
Points: 289, Visits: 683

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

opc.three

Posted Wednesday, February 20, 2013 7:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1422070

Resender

Posted Wednesday, February 20, 2013 7:48 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:55 AM
Points: 289, Visits: 683

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] + "\\"

@[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

opc.three

Posted Wednesday, February 20, 2013 8:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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 Wink

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1422106

Jeff Moden

Posted Wednesday, February 20, 2013 8:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1422108

« Prev Topic | Next Topic »

Permissions