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

mainrotor
mainrotor used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial