StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
7140168
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-08-21T18:24:34.390
FavoriteCount
0
LastActivityDate
2011-08-22T09:34:23.523
LastEditDate
2011-08-22T09:34:23.523
LastEditorUserId
0
OwnerUserId
0
ParentId
7139172
PostTypeId
2
Score
29
ViewCount
0
LastEditorDisplayName
user756519
text
Body
Here is one possible solution to achieve this with the help of <code>Foreach loop container</code>, <code>Script Task</code> and <code>File System Task</code>. You can do this without File System Task. However, I have used it to make use of the in-built control flow task to move the files. The example was created using <code>SSIS 2005</code>. The example assumes that the files will be named uniformly. So, the example uses the format File DD-MM-YYYY. For example, the files will be named <code>File 29-07-2011</code>, <code>File 15-08-2011</code> etc. On the SSIS package, create the following variables. In this example, the source files are stored in the folder location <code>F:\Temp\</code> and the files should be moved to the location *F:\Temp\Monthwise*. Within the destination folder, there will be folders for each month like July, August etc. <ul> <li>DestinationFolder variable will hold the final destination folder value like <code>F:\Temp\Monthwise\August</code> but this variable will be assigned with the actual value inside the Script task. For now, let's assign the value <code>F:\Temp\Monthwise\</code>. This temporary value is to avoid File System Task from throwing error messages at design time.</li> <li>DestinationRoot will contain the actual root folder under which the folders like July, August should be created based on the month names. </li> <li>SourceFolder denotes the folder in which all the files are initially stored. Here in this example, the source folder will be <code>F:\Temp\</code></li> <li>SourceFilePath denotes the actual file path. This variable will be assigned with the individual file values when the Foreach loop container loops through each variable. To avoid the File System Task from throwing error messages at design time, let's assign it with some dummy value <code>F:\Temp\1.txt</code>.</li> <li>FilePattern defines the file pattern that should be looped through in the given source folder path. Let's assign <code>*.*</code>, which means all the files will be looped through. You can also specify <code>*.txt</code> or <code>File*.txt</code> or <code>My*.xls</code> etc. It is upto your requirements.</li> <li>MonthStartPosition denotes the position where the month value starts in the file name. So, in the file name format <code>File 29-07-2011</code>, the month 07 starts at 9th character. Hence the value 9.</li> <li>MonthLength specifies the number of character to extract. This will anyways be 2 characters but I didn't want to hard code. So, I created a variable.</li> <li>MonthNameFormat specifies how the folders should be created. Value MMMM denotes that it will create the folders with full month names like January, February etc. If we use the value MMM, the folders will be created as Jan, Feb etc. The folders will be created only if they didn't exist.</li> </ul> <img src="https://i.stack.imgur.com/yk7b1.png" alt="Variables"> On the SSIS package's Control Flow tab, place a <code>Foreach loop container</code> and configure it to loop through the folder specified in the variable <code>SourceFolder</code> using the file pattern variable <code>FilePattern</code>. As the Foreach loop container loops through the files, the file names will be assigned to the variable SourceFilePath. We will use this variable to fetch the month value in Script Task. <img src="https://i.stack.imgur.com/MYNWq.png" alt="Foreach loop container General"> <img src="https://i.stack.imgur.com/lTDEe.png" alt="Foreach loop container Collection"> <img src="https://i.stack.imgur.com/vpzbb.png" alt="Foreach loop container Variable Mappings"> Within the Foreach loop container, place a <code>Script Task</code> and on the script task's Script section click the Design script... button to open the VSTA editor and paste the code provided after these screenshots. Since the example was created in VS 2005, the code is written in VB.NET because that is the only supported language in <code>SSIS 2005</code>. <img src="https://i.stack.imgur.com/5CUZh.png" alt="Script Task General"> <img src="https://i.stack.imgur.com/RpmI7.png" alt="Script Task Script"> <img src="https://i.stack.imgur.com/k1FWR.png" alt="Script Task Code"> Script Task Code: The code gets the full file path value from the variable <code>SourceFilePath</code> and extracts only the file name to store it in the local variable <code>FileName</code>. Then checks to see if the <code>MonthStartPosition</code> and <code>MonthLength</code> variables are assigned with proper non-zero values. It then extracts the month value to store it in the local variable <code>MonthValue</code>. Uisng the <code>MonthValue</code>, it fetches the full month name value using the DateTime function. The values 1 are assigned to day and year because we only want the Month name. The month name in the local variable FolderName is combined with the DestinationRoot value to check if the folder exists or not. If the folder doesn't exist, the folder will be created so that the File System Task doesn't fail. Finally the full destination folder value is assigned to the package variable <code>DestinationFolder</code>. This variable will be used in the File System Task. <code>VB.NET code for SSIS 2005</code> <pre><code>Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim varCollection As Variables = Nothing Dts.VariableDispenser.LockForRead("User::SourceFilePath") Dts.VariableDispenser.LockForRead("User::DestinationRoot") Dts.VariableDispenser.LockForRead("User::MonthStartPosition") Dts.VariableDispenser.LockForRead("User::MonthLength") Dts.VariableDispenser.LockForRead("User::MonthNameFormat") Dts.VariableDispenser.LockForWrite("User::DestinationFolder") Dts.VariableDispenser.GetVariables(varCollection) Dim SourceFilePath As String = varCollection("User::SourceFilePath").Value.ToString() Dim FileName As String = SourceFilePath.Substring(SourceFilePath.LastIndexOf("\") + 1) Dim DestinationRoot As String = varCollection("User::DestinationRoot").Value.ToString() Dim MonthStartPosition As Integer = Convert.ToInt32(varCollection("User::MonthStartPosition").Value) Dim MonthLength As Integer = Convert.ToInt32(varCollection("User::MonthLength").Value) Dim MonthValue As Integer = 0 Dim MonthNameFormat As String = varCollection("User::MonthNameFormat").Value.ToString() Dim FolderName As String = String.Empty Dim MonthwiseDirectory As String = String.Empty If MonthStartPosition > 0 AndAlso MonthLength > 0 Then MonthValue = Convert.ToInt32(FileName.Substring(MonthStartPosition - 1, MonthLength)) End If If FileName.Length > 0 AndAlso MonthValue > 0 Then FolderName = New DateTime(1, MonthValue, 1).ToString(MonthNameFormat) End If MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName) If Not System.IO.Directory.Exists(MonthwiseDirectory) Then System.IO.Directory.CreateDirectory(MonthwiseDirectory) End If varCollection("User::DestinationFolder").Value = MonthwiseDirectory Dts.TaskResult = Dts.Results.Success End Sub End Class </code></pre> <code>C# code for SSIS 2008 and above</code> <pre><code>public void Main() { Variables varCollection = null; Dts.VariableDispenser.LockForRead("User::SourceFilePath"); Dts.VariableDispenser.LockForRead("User::DestinationRoot"); Dts.VariableDispenser.LockForRead("User::MonthStartPosition"); Dts.VariableDispenser.LockForRead("User::MonthLength"); Dts.VariableDispenser.LockForRead("User::MonthNameFormat"); Dts.VariableDispenser.LockForWrite("User::DestinationFolder"); Dts.VariableDispenser.GetVariables(ref varCollection); string SourceFilePath = varCollection["User::SourceFilePath"].Value.ToString(); string FileName = SourceFilePath.Substring(SourceFilePath.LastIndexOf('\\') + 1); string DestinationRoot = varCollection["User::DestinationRoot"].Value.ToString(); int MonthStartPosition = Convert.ToInt32(varCollection["User::MonthStartPosition"].Value); int MonthLength = Convert.ToInt32(varCollection["User::MonthLength"].Value); int MonthValue = 0; string MonthNameFormat = varCollection["User::MonthNameFormat"].Value.ToString(); string FolderName = string.Empty; string MonthwiseDirectory = string.Empty; if (MonthStartPosition > 0 && MonthLength > 0) { MonthValue = Convert.ToInt32(FileName.Substring(MonthStartPosition - 1, MonthLength)); } if (FileName.Length > 0 && MonthValue > 0) { FolderName = new DateTime(1, MonthValue, 1).ToString(MonthNameFormat); } MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName); if (!System.IO.Directory.Exists(MonthwiseDirectory)) { System.IO.Directory.CreateDirectory(MonthwiseDirectory); } varCollection["User::DestinationFolder"].Value = MonthwiseDirectory; Dts.TaskResult = (int)ScriptResults.Success; } </code></pre> Within the Foreach loop container, place File System Task after the Script task. Configure the File System Task as shown in the screenshot. <img src="https://i.stack.imgur.com/fvuu0.png" alt="File System Task"> Once the package tasks are configured, the Control Flow tab should look like as shown below. <img src="https://i.stack.imgur.com/yShdf.png" alt="Control Flow"> Let's test the package. Before that, the contents of the source folder F:\Temp are shown below. The files are dummy. Hence, the size 0 KB. <img src="https://i.stack.imgur.com/PYNt7.png" alt="F Temp Folder"> Below screenshot shows the successful execution of the package. <img src="https://i.stack.imgur.com/lt7DM.png" alt="Success"> Below screenshots show how the files have been moved to respective destination folder that have been created based on the month names. Contents of the individual folders are shown below. Hope that helps. <img src="https://i.stack.imgur.com/BoQyz.png" alt="F Temp"> <img src="https://i.stack.imgur.com/tY4rP.png" alt="F Temp Monthwise"> <img src="https://i.stack.imgur.com/jiqtr.png" alt="F Temp Monthwise August"> <img src="https://i.stack.imgur.com/X3Eiu.png" alt="F Temp Monthwise January"> <img src="https://i.stack.imgur.com/89TQn.png" alt="F Temp Monthwise July">
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow do I create a package that would copy all files from a given folder into a new folder?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. This table or related slice is empty.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POHow do I create a package that would copy all files from a given folder into a new folder?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.