I need help creating an SSIS package in SQL Server 2008R2

Hi Experts,
I need to create and SSIS package in SQL Server 2008 R2for an EDI project .  The package needs to import data from one or more .csv file(s) into a table in a SQL Server 2008 R2 database.
The files are need to be retrieved from the following path:  \\EDIFiles\SFTP\TARGET .
The .csv files have 20 columns, but only 5 on those columns need to be imported into the SalesOrder table.
Once each file is processed, it needs to be archived in the following folder:  \\EDIFiles\SFTP\TARGET\Archive.

How do I do this?  Thank you very much in advance,
mrotor
mainrotorAsked:
Who is Participating?
 
Barry CunneyCommented:
Hi MRotor
In SSIS 'Control Flow' tab you can add a 'Foreach Loop Container' - set the Eumerator to 'Foreach File Enumerator'
and in the Enumerator Configuration set the 'Folder' to \\EDIFiles\SFTP\TARGET(Note: if this is a remote file share it may be best to to move these locally first - 'File System Task' can be used for this) and set the 'Files' to *.csv
Create and Map a variable in this Loop Container that will receive the filename on each iteration

Insert a 'Data Flow Task' into the center of the above Foreach Loop Container
Create a flat file connection manager which gets its Connection String value from the variable mapped in the For Each Loop above  - set this flat file connection as the Source to Data Flow Task and set the SQL Server table as the Destination

For the archiving use a FileSystem Task to move the files - this can also be placed in the ForEach Loop and a precedence constraint joining it from the Data Flow task so that files only get archived after they have been imported.
0
 
mainrotorAuthor Commented:
Thanks Barry,
I have gotten everything to work except for the archiving portion.  I will try tomorrow and let you know if it worked.

Once I finish this SSIS package, how do I deploy it and get it ready to use within a SQL Server Job?  Thanks once again in advance.

mrotor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.