Scenario:
Let’s say we load data from flat file on daily basis. There
is SaleAmount Column that should have only integer (numbers). If somehow the
wrong data is provided in that column we do not want to fail the package
instead of that we want to redirect those rows to error output.
Solution:
Different Transformations in SSIS Provide the error output
in case the operation cannot be perform on input data. We can redirect error
rows from Different sources, transformations and destinations as well. In out example
we are using flat file source.
Step 1:
Create connection to text file by using Flat File Connection
Manager. I have used below data for initial testing
CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,300
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,Anna,200
Mexico,Anthony,500
Change the data type to Integer in Flat File Source ( connection Manager) as Shown below
Step 2:
After choosing the correct data types for the inputs, go to Error Output Table on Left and then configure as shown below
Step 3:
Include two multicast transformation just to test your package with correct data as given above. All the records will go to Success Output as shown below
Step 4:
Let's add one more row to input file with wrong SaleAmount
Mexico,Anthony,test500 and execute the package.
As we can see that the error row is redirect to error output and rest of rows went to success output. SSIS does not provide a column name due to which the row is redirected.Custom Code can be written to find out more details about column name and description. In this example we can go back to our flat file and check the data manually or we can load this file into some staging table and write Tsql queries to validate and find out which row does not have valid data and got rejected in loading process.
I think there is a need to look for some more information about SSIS and its other useful aspects.
ReplyDeleteSSIS PostgreSql Write
Aivivu đại lý vé máy bay, tham khảo
ReplyDeletevé máy bay đi Mỹ hạng thương gia
vé máy bay từ mỹ về việt nam hãng ana
ve may bay tư duc ve viet nam
vé máy bay giá rẻ từ nga về việt nam
các chuyến bay từ anh về việt nam
chuyến bay từ paris về hà nội
India has got a long list of Private and Government Banks which do support the financial needs of everyone in the country. India is the second largest country in terms of population and to meet everyone’s needs. There are numerous Banks established in India. In general the India Bank Holidays are normal on public Holidays, which applied through Nationwide. Though each bank in India has different regulations, Bank of India balance enquiry number all of them follow the same pattern of bank holidays in India for the given year.So if you are wondering what the list of bank holiday this month, then you should read this article. Where we talk about the holidays for the Banks in India. Do prefer different holidays as per their state culture and defined holidays.
ReplyDelete