question

Rosalina5-9376 avatar image
0 Votes"
Rosalina5-9376 asked YitzhakKhabinsky-0887 edited

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

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





dotnet-csharpsql-server-transact-sqlsql-server-integration-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is a very large ask for a support forum. I think the best way to get started is to know where the SSIS documentation is located...

SQL Server Integration Services

...then go through a few tutorials.

Most of SSIS is drag and drop. However, you'll most likely need a 3rd party library for SFTP.

Lesson 1: Create a project and basic package with SSIS


1 Vote 1 ·

@Rosalina5-9376,

What happened to your requirements

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

You accepted an answer without any of it.

0 Votes 0 ·
Zoehui-MSFT avatar image
1 Vote"
Zoehui-MSFT answered

Hi @Rosalina5-9376,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
2 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @Rosalina5-9376,

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;

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>


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.