May 20, 2013 at 5:16 am
Here is the 2nd task I want to do
I have 10 folders from 10 services. Each of these services can drop any file into their own folder. The Files MUST start with 4 numbers, e.g. 1097.
These files need to be copied into the corresponding Number (So a folder named 1097)
Ive got 400 of these numbered folders.
I have a SQL Script creating the variable for each folder.
I want to feed this into a file task which goes to that folder. Splits the files into the specific directories based on LEFT(4,FileName)
If they have got it wrong its added to a misc folder.
Is this do - able in Integration Services?
SO
source Folder File Destination Folder
Service VA > 2300_VA_Example_File.xls > 2300
> 2311_VA_Example_File.doc >2311
Service MA > 3456_MA_Example_File.csv > 3456
> 2300_MA_Example_File.ppt > 2300
May 20, 2013 at 5:22 am
Of course - almost everything is possible in SSIS, given enough development effort 🙂
How's your C#? I'd use a Script Task for this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2013 at 5:26 am
Ahhh, C# skills arent up to much.
I could possibly figure it out if I had an example to work from.
Is C# the only way to go on this?
Debbie
May 20, 2013 at 5:44 am
Debbie Edwards (5/20/2013)
Ahhh, C# skills arent up to much.I could possibly figure it out if I had an example to work from.
Is C# the only way to go on this?
Debbie
I'd say that C# or VB would be the cleanest solutions. Or if you have skills in any other scripting language (eg, VBScript, Powershell), it may be possible to call those scripts from within SSIS.
It may be possible to use a combination of
1) FEL to scan all the files in the source folders.
2) Derived variable or variables (based on the filepath returned by the FEL) to point at the target path (including special coding for invalid files)
3) File System task to move the file.
Part (2) will be quite tricky.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2013 at 5:58 am
Phil Parkin (5/20/2013)
Debbie Edwards (5/20/2013)
Ahhh, C# skills arent up to much.I could possibly figure it out if I had an example to work from.
Is C# the only way to go on this?
Debbie
I'd say that C# or VB would be the cleanest solutions. Or if you have skills in any other scripting language (eg, VBScript, Powershell), it may be possible to call those scripts from within SSIS.
It may be possible to use a combination of
1) FEL to scan all the files in the source folders.
2) Derived variable or variables (based on the filepath returned by the FEL) to point at the target path (including special coding for invalid files)
3) File System task to move the file.
Part (2) will be quite tricky.
That would be beyond my capabilites. I dont know what FEL is? What I need is a step by step guide . Ive found this http://stackoverflow.com/questions/15287943/how-to-move-files-to-different-folders-based-on-matching-filename-and-folderna
and I'm going through this to see if it could help me but I wouldnt be able to user the above info. There must be a few ways of doing this? There must be a slightly easier way of doing this? Maybe I could include a folder to put everything in from every service folder before adding to each number folder?
May 20, 2013 at 6:19 am
I apologise for the jargon. FEL is just an abbreviation for 'Foreach Loop Container' - it is used quite often here.
The example you mentioned looks like a good place to start & develop your understanding of what can be done. Good luck & post back with any questions,
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2013 at 8:04 am
Still no where close to coming up with even a starting point for this.
Ive gone for the basic option and created a small example folder structure. So I have 3 folders
AA
BB
CC
Each of these folder have 3 files in them as a test.
E.g 1111_AA_Test.doc 1112_AA_Test.XLS 1113_AA_Test.csv
1111_BB_Test.doc 1112_BB_Test.XLS 1113_BB_Test.csv
1111_CC_Test.doc 1112_CC_Test.XLS 1113_CC_Test.csv
And then Hotfolders\Downloads\1111
Hotfolders\Downloads\1112
Hotfolders\Downloads\1113
So I create a ForeachLoop with SourceFolder as a variable.
It goes to the first folder AA
In another Loop
Takes the name of file 1 and Inserts the name into a SQL table
Takes the name of file 2 and inserts the name into a SQL Table
And so on till there are no more files left
In Another Loop using DestFolder as a variable
Looks at File 1. It starts with 1111 so it goes into folder Hotfolders\Downloads\1111
Looks at File 2. It starts with 1112 so it goes into folder Hotfolders\Downloads\1112
Looks at File 3. It starts with 1113 so it goes into folder Hotfolders\Downloads\1113
no more files so it exists the loop
It goes back up to the top of the loop to the next folder BB
and so on until its done.
So Ive got a good idea what is required but no idea how to tackle it
I've got three variables so far SourceFolder, DestFolder and CODE_NAME (1111, 1112,1113 etc)
Ive tried a few bow to guides and got no where so far. I really really want to avoid writing script. Im fine in SQL but I haven't had training on other programming languages.
May 20, 2013 at 9:30 am
I think you might be overdoing the looping.
If your source folder structure looks something like this:
root
root\AA
root\BB
root\CC
etc
then you can configure your FEL to point at folder <root> and check the 'Traverse subfolders' option.
Within your FEL, add an ExecuteSQL task to insert the name of the file to a SQL Server table.
Also within your FEL, add a File System task to move the 'current' file:
> IsDestinationPathVariable : True
> DestinationVariable : use your dynamic variable here.
This method does not, unfortunately, give you any more than the name of the file to put into your SQL Server file list table.
Also, you'll need to get clever with Expressions to be able to build the destination for your file move.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2013 at 9:44 am
I'm having issues just doing an initial test at the moment. I decided to just do a much smaller version before attempting anything bigger
File System Task Access to path is denied
I set up a ForEachLoop
Foreach File Enumerator
I add in the folder Name \\D-ff73\fis-it\PROJECTS\IS_Test\AA
And the files are fully qualified as 1111*.* (Any file that starts with 1111)
In Variable Mappings I have mapped to SourceFolderName 0 . This is also \\D-ff73\fis-it\PROJECTS\IS_Test\AA (I had hoped doing this overwrites the FolderName as its using a variable but it doesn’t.)
Within the For Each Loop is a file system task.
The Source Connection uses the variable User::SourceFolderName
The Destination Connection uses the variable User::DestFolderName
User::DestFolderName was set up to \\D-ff73\fis-it\PROJECTS\IS_Test\Downloads\1111
When I run this the file that starts with 1111 is copied from AA to 1111
However obviously I don’t want to do this as I would have to set up hundreds of them for each source and destination.
So
I create a SQL Script that contains 3 folder names
SELECT ‘\\D-ff73\fis-it\PROJECTS\IS_Test\AA’ UNION
SELECT ‘\\D-ff73\fis-it\PROJECTS\IS_Test\BB’ UNION
SELECT ‘\\D-ff73\fis-it\PROJECTS\IS_Test\CC’ UNION
I then Create a ResultsSet Result Name 0 Variable Name User::CreateSourceFolderNameVariable
This Is an object variable and holds the Source folder name.
This is connected to the original forEach loop
Then in the ForEach Loop I change it to The Collection of For Each Ado Enumerator
I create the Ado Object Source variable of CreateSourceFolderNameVariable (As this is where the source file is stored)
In variable Mappings I have User::SourceFolderName as this is coming out of the CreateSourceFolderNameVariable Index 0
When I run it I get
[File System Task] Error: An error occurred with the following error message: "Access to the path '\\D-ff73\fis-it\PROJECTS\IS_Test\AA' is denied.".
But it does work as I've shown in the first test. Maybe the error is confusingly named? Im not sure but as soon as you change to the parameters I get this issue
I thought Id start with something simple before starting with the loops but I appear to have been snagged with another issue
May 20, 2013 at 9:59 am
As you can see from the last message Im not even at this stage yet but...
Phil Parkin (5/20/2013)
I think you might be overdoing the looping.If your source folder structure looks something like this:
then you can configure your FEL to point at folder <root> and check the 'Traverse subfolders' option.
Im unsure what you mean by this. Im guessing I need more do A B and C instructions.
Within your FEL, add an ExecuteSQL task to insert the name of the file to a SQL Server table.
Again Where can I find instructions on how to do this?
Also within your FEL, add a File System task to move the 'current' file:
> IsDestinationPathVariable : True
> DestinationVariable : use your dynamic variable here.
Again I need a how to on this as I dont really understand what Im supposed to be doing. Clearly I need to be much more skilled to use these instructions :blush:
This method does not, unfortunately, give you any more than the name of the file to put into your SQL Server file list table
If this works I will certainly take it!.
Also, you'll need to get clever with Expressions to be able to build the destination for your file move.
I'm very happy to look into expressions but I'm not sure what you mean by building the destination for the file move?
If only there was a clear how to guide to do everything I needed to do on this?
May 21, 2013 at 7:37 am
Looping Through and Loading Files with SSIS < while you're not going to be loading the files into a adatabase this video will show you how to configure a FeLC (aka FEL) to iterate over files in a directory. the stuff relevant for you starts at 2:05
Using the File System Task in SSIS
< This video will show you how to use the File System Task.
By watching these two videos hopefully it will fill in some knowledge gaps and help you to a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply