Design and Develop SSIS Package to perform Cleansing and Validation Task.

Question

How to Develop a SSIS Package which can perform task like:-

1. Received File from SFTP, the File Format is .csv.
2. Load the File to Existing table in sql server
3. Perform Validation and Checks
4. Generate the Clean .csv
5. Upload the file to customer SFTP
6. Logging and Error Handling with table creation in sql server

I am very new to SSIS, Appreciate your help and Guidence.

Thanks a lot

Answer 1

Hi @Rosalina5 ,

Please refer to this link about how to load data from SFTP.
https://stackoverflow.com/questions/66242015/download-sftp-file-using-ssis-package

And then you may easily load the csv file to table.

lesson-adding-and-configuring-an-ole-db-connection-manager

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Rosalina5 ,

SSIS doesn't have a built-in task for SFTP operations.
Many are using WinSCP for that: Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS)
It is free of charge.

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data.
The most reliable format for data feeds is XML enforced by XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, and enforce data quality.

SSIS has the following components for that;

• XML Source Adapter: xml-source
• XML Task, Operation Validation: validate-xml-with-the-xml-task

Sample output for XML that's not valid.
It shows exact errors location, line and position, and explicit error message for each of them.

<root xmlns:ns="https://schemas.microsoft.com/xmltools/2002/xmlvalidation">    
    <metadata>    
        <result>false</result>    
        <errors>2</errors>    
        <warnings>0</warnings>    
        <startTime>2015-05-28T10:45:09.538</startTime>    
        <endTime>2015-05-28T10:45:09.558</endTime>    
        <xmlFile>C:\Temp\TestData.xml</xmlFile>    
        <xsdFile>C:\Temp\TestSchema.xsd</xsdFile>    
    </metadata>    
    <messages>    
        <error line="5" position="26">The 'ApplicantRole' element is invalid - The value 'wer3' is invalid    
    according to its datatype 'ApplicantRoleType' - The Enumeration constraint failed.</error>    
        <error line="16" position="28">The 'Phone' element is invalid - The value 'we3056666666' is invalid    
     according to its datatype 'phone' - The Pattern constraint failed.</error>    
    </messages>    
</root>