Solved

SSIS 2008

Posted on 2014-03-29
2
228 Views
Last Modified: 2016-02-10
Can you please answer below questions. please add your answer inline

Q1) In a Flat File there are millions of records. It consists of many
rejections data. Now if rejections are
a) from 0% to %25 of rejections, then continue process
b) from 25% to 40% of rejections, then Trigger a email
c) from 40% to 50% of rejections, then wait for user to respond
d) > 50% of rejections, then Abort the sequence

Q2) Suppose I want to execute a sql task before that i want to do some
job, how you will do?

Q3) Revenue Report has daily transactions of million and billions of
revenue records of different category as MMS Revenue, Internet Calling
Revenue etc. Want report of revenue received from 1 to 30 days. How to
do?

Q4) In one single flat file the data of customers are given in multiple
record. for e.g. first record has cust name, cust date
second record has cust address, cust phone
third record has cust email, cust status
How you will create one record destination from three records?
0
Comment
Question by:BeyondBGCM
  • 2
2 Comments
 
LVL 9

Expert Comment

by:edtechdba
ID: 39964255
This may point you in the right direction ...

Q1) I would create an Execute SQL task that directly queries the data set that is going to be returned into the flat file, and assign a value of 1, 2, 3 or 4 depending on the percentage rates. Then return the value from the SQL to a variable within the package. This way you'll have a specific indicator of which percentage range is being returned.

For example: 1 = 0 - 25%, 2 = 26 - 40%

Then use conditional logic based on the variable value (populated from the Execute SQL task) to determine which direction the process should take.

Q2) Here's an example of how to return integer values from a SQL query.

SELECT (CASE WHEN ([rejections]\[totalamount]) <= .25 THEN 1
                           WHEN ([rejections]\[totalamount]) > .25
                              AND  ([rejections]\[totalamount]) <= .40 THEN 2
                            -- and so on ..
                           END) AS rejectionrate
FROM [yourtable]
0
 
LVL 9

Accepted Solution

by:
edtechdba earned 500 total points
ID: 39964259
Q3) If you are running this within an SSIS package, I would create a data flow task, use the source task to execute a stored procedure from the database, and query the 30 day period from the stored procedure. Since I don't have any table or column information to work with, I'm unable to give you an example of how to filter out that time period.

Q4) I would take this flat file, import the data into a database table, then use a stored procedure to get all of the appropriate data columns (cust name, cust date, etc.) into the proper format.

Then use a data flow task to execute a stored procedure to return the data in the proper format and use this to populate a flat file (or whichever type of destination task you would like to use to contain this data).

I hope this helps!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql user function 7 31
SQL Query 2 43
Storage Spaces 3 39
SQL Server stored proc 2 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now