SQL Server Performance

How to Combine Output of Several Queries into One Text File in SSIS

Discussion in 'SQL Server 2005 Integration Services' started by lcerni, May 3, 2011.

  1. lcerni New Member

    I am really new to SSIS and struggling.
    I have to create a text file apending the output of several queries.
    How do I use SSIS to create this text file. I have several queries that look like the below.
    I am so confused. For the Control Flow tab I have a Data Flow Task. Is this the correct thing to do?
    However, for the Data Flow tab I have seperate Source - Query and Destination - Text but I don't think this is correct. How do I append to the text file the results of each query?
    --============================================QUERIES
    DECLARE @ValuateDate DATETIME
    SET @ValuateDate = '2010-12-31 00:00:00.000'
    SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,'
    SELECT ',,,,,,,,,,,,,,,,,,,'

    --============================================
    DECLARE @AsOfDate DATETIME
    SET @AsOfDate = '2010-12-31 00:00:00.000'
    SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,'
    UNION ALL
    SELECT CONVERT(VARCHAR, xdate, 112) + ',' +
    xperiod + ',' +
    CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,'
    FROM RC
    WHERE [Status] = 'A'
    AND xdate = @AsOfDate
    SELECT ',,,,,,,,,,,,,,,,,,,'
    --============================================

    DECLARE @yDate DATETIME
    SET @yDate = '2010-12-31 00:00:00.000'
    SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
    UNION ALL
    SELECT yperiod + ',' +
    CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,'
    FROM DY
    WHERE [Status] = 'A'
    AND yDate = @yDate
    SELECT ',,,,,,,,,,,,,,,,,,,'
    --============================================

    CREATE TABLE #TTXYZ (OutputXYZ VARCHAR(2000))
    INSERT INTO #TTXYZ
    SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200'
    DECLARE @FileDate DATETIME
    SET @FileDate = '2010-12-31 00:00:00.000'
    INSERT INTO #TTXYZ
    SELECT
    CONVERT(VARCHAR(250), A.Term )+ ',' +
    CONVERT(VARCHAR(250), A.[40]) + ',' +
    CONVERT(VARCHAR(250), A.[50]) + ',' +
    CONVERT(VARCHAR(250), A.[60]) + ',' +
    CONVERT(VARCHAR(250), A.[70]) + ',' +
    CONVERT(VARCHAR(250), A.[75]) + ',' +
    CONVERT(VARCHAR(250), A.[80]) + ',' +
    CONVERT(VARCHAR(250), A.[85]) + ',' +
    CONVERT(VARCHAR(250), A.[90]) + ',' +
    CONVERT(VARCHAR(250), A.[95]) + ',' +
    CONVERT(VARCHAR(250), A.[100])+ ',' +
    CONVERT(VARCHAR(250), A.[105])+ ',' +
    CONVERT(VARCHAR(250), A.[110])+ ',' +
    CONVERT(VARCHAR(250), A.[115])+ ',' +
    CONVERT(VARCHAR(250), A.[120])+ ',' +
    CONVERT(VARCHAR(250), A.[125])+ ',' +
    CONVERT(VARCHAR(250), A.[130])+ ',' +
    CONVERT(VARCHAR(250), A.[140])+ ',' +
    CONVERT(VARCHAR(250), A.[150])+ ',' +
    CONVERT(VARCHAR(250), A.[200])
    FROM
    (
    SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
    FROM
    (SELECT Term, Percentile, Strike
    FROM XYZ
    WHERE [Status] = 'A'
    AND FileDate=@FileDate
    ) ps
    PIVOT
    (
    SUM (Strike)
    FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
    ) AS pvt
    ) A,
    (
    SELECT 'Terms' AS Term, 0 AS [Order]
    UNION All
    SELECT '1M' AS Term, 1 AS [Order]
    UNION All
    SELECT '3M', 2
    UNION All
    SELECT '6M', 3
    UNION All
    SELECT '9M', 4
    UNION All
    SELECT '1Y', 5
    UNION All
    SELECT '2Y', 6
    UNION All
    SELECT '3Y', 7
    UNION All
    SELECT '4Y', 8
    UNION All
    SELECT '5Y', 9
    UNION All
    SELECT '6Y', 10
    UNION All
    SELECT '7Y', 11
    UNION All
    SELECT '8Y', 12
    UNION All
    SELECT '9Y', 13
    UNION All
    SELECT '10Y', 14
    ) B
    WHERE A.Term = B.Term
    ORDER BY [Order]
    SELECT * FROM #TTXYZ
    SELECT ',,,,,,,,,,,,,,,,,,,'
    DROP TABLE #TTXYZ
    --============================================
    The text file should look something like this:
    Valuate Date,,12/31/2010,,,,,,,,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    AsOfDate,,MidRate,,,,,,,,,,,,,,,,,
    20101231,1D,0.125,,,,,,,,,,,,,,,,,
    20101231,1M,0.13,,,,,,,,,,,,,,,,,
    20101231,2M,0.10412,,,,,,,,,,,,,,,,,
    20101231,3M,0.22345,,,,,,,,,,,,,,,,,
    20101231,40Y,2.11258,,,,,,,,,,,,,,,,,
    20101231,41Y,2.1175,,,,,,,,,,,,,,,,,
    20101231,42Y,2.13,,,,,,,,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    DivDate,MidRate,,,,,,,,,,,,,,,,,,
    1Y,1.0158,,,,,,,,,,,,,,,,,,
    2Y,1.1511,,,,,,,,,,,,,,,,,,
    3Y,1.2997,,,,,,,,,,,,,,,,,,
    4Y,1.3615,,,,,,,,,,,,,,,,,,
    5Y,1.7253,,,,,,,,,,,,,,,,,,
    6Y,1.4961,,,,,,,,,,,,,,,,,,
    7Y,1.3326,,,,,,,,,,,,,,,,,,
    8Y,1.6157,,,,,,,,,,,,,,,,,,
    9Y,1.2117,,,,,,,,,,,,,,,,,,
    10Y,1.9524,,,,,,,,,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    TERMS,40,50,60,70,75,80,85,90,95,100,105,110,115,120,125,130,140,150,200
    1M,81.15,69.39,59.21,49.87,45.24,40.68,37.12,31.5,26.18,20.53,15.76,14.85,17.14,13.44,13.95,14.46,15.26,15.75,15.75
    3M,59.28,52.11,45.49,39.1,35.95,32.84,29.94,27.02,23.63,20.34,18.02,15.4,13.83,13.63,13.84,14.07,14.56,14.82,14.84
    6Y,35.86,33.35,31.09,29.1,28.16,27.23,26.38,25.53,24.75,23.98,23.27,22.57,21.93,21.31,20.75,20.21,19.27,18.43,15.91
    7Y,35.9,33.53,31.42,29.57,28.69,27.83,27.04,26.26,25.53,24.82,24.16,23.51,22.91,22.33,21.8,21.29,20.38,19.57,16.87
    8Y,36.05,33.85,31.88,30.15,29.34,28.54,27.8,27.08,26.4,25.73,25.11,24.5,23.94,23.39,22.89,22.39,21.52,20.72,17.88
    9Y,36.21,34.15,32.3,30.68,29.91,29.16,28.47,27.79,27.15,26.52,25.93,25.36,24.82,24.3,23.82,23.35,22.5,21.73,18.8
    10Y,36.36,34.42,32.67,31.14,30.42,29.71,29.05,28.41,27.8,27.21,26.65,26.11,25.6,25.1,24.64,24.19,23.37,22.62,19.66
    ,,,,,,,,,,,,,,,,,,,
    TERMS,1Y,2Y,3Y,4Y,5Y,7Y,10Y,15Y,20Y,25Y,30Y,,,,,,,,
    1M,120.6,95.5,80.1,65.7,58.4,45.5,38.1,31,28.5,26.8,26.55,,,,,,,,
    3M,108.3,86.7,71,59.2,53.6,42.1,35,29.7,27.6,27.06,26.01,,,,,,,,
    6M,101.9,78,62.7,53,48,38.9,32.9,27.9,26.1,25.47,24.55,,,,,,,,
    20Y,17.1,16.9,16.5,16.2,15.9,15.7,15.5,14.6,14.2,14.2,14.2,,,,,,,,
    25Y,16.3,16.2,15.9,15.6,15.3,15.2,15.1,14.6,14.5,14.6,14.5,,,,,,,,
    30Y,16.1,16,15.8,15.5,15.2,15.3,15.5,15.2,15.1,15,14.8,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    ABC,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
    3M,-11.10642722,-11.10642722,-11.10642722,-5.034760873,0,2.020418172,6.751897694,11.17845703,14.41822452,,,,,,,,,,
    6M,-4.061528263,-4.061528263,-4.061528263,-2.190803914,0,1.007284799,3.602992542,6.275349445,8.346291692,,,,,,,,,,
    7Y,9.704440423,4.95659497,1.826853579,0.76317559,0,-0.279144977,-0.772692213,-0.856299565,-0.584548035,,,,,,,,,,
    10Y,9.130007634,4.953796941,1.948158101,0.842153631,0,-0.324276784,-0.945269402,-1.142308261,-0.929713449,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    DEF,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
    3M,1.931776901,1.931776901,-3.382620316,-1.777061882,0,0.838619806,3.064402039,5.431664422,7.299156372,,,,,,,,,,
    7Y,9.880792642,5.179110337,1.972568598,0.840461905,0,-0.317995112,-0.916605968,-1.097951702,-0.882094443,,,,,,,,,,
    10Y,9.459319348,5.24784503,2.126397839,0.936019485,0,-0.371497208,-1.119129386,-1.426048012,-1.266737679,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    WSA,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
    3M,22.55034953,6.28060709,0.744749395,-0.017592948,0,0.190959777,1.131886745,2.66980541,4.168811468,,,,,,,,,,
    6M,18.07934327,5.518183038,0.96549076,0.18809114,0,0.052355906,0.54368373,1.536622924,2.593729168,,,,,,,,,,
    7Y,10.62443244,5.816540324,2.343224259,1.031917981,0,-0.412016685,-1.257564234,-1.649180639,-1.532972916,,,,,,,,,,
    10Y,10.2638564,5.852399925,2.4643242,1.109968746,0,-0.456779748,-1.423193984,-1.891203496,-1.780995164,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    QWE,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
    3M,24.16571236,11.00234028,3.616140238,1.372458127,0,-0.391544886,-0.667506579,0.14072811,1.383460652,,,,,,,,,,
    6M,19.91175493,9.145274204,3.081024577,1.210736786,0,-0.3870637,-0.86677434,-0.527821288,0.264048438,,,,,,,,,,
    7Y,11.64557121,6.407356397,2.580801365,1.127486247,0,-0.435819166,-1.246404662,-1.423328511,-1.062914852,,,,,,,,,,
    10Y,11.17357136,6.365218906,2.660755918,1.183204244,0,-0.465777829,-1.330438184,-1.478521941,-1.062087615,,,,,,,,,,
    ,,,,,,,,,,,,,,,,,,,
    KJH,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
    3M,26.4342366,12.98064585,4.792483456,1.987329468,0,-0.686653993,-1.655899651,-1.293878676,-0.212921367,,,,,,,,,,
    6M,21.78094891,10.72050444,3.987423025,1.677647978,0,-0.611560644,-1.645687343,-1.71856794,-1.102346407,,,,,,,,,,
    7Y,12.12092879,6.673612202,2.693369334,1.176688393,0,-0.453003854,-1.280840196,-1.425003492,-1.018638868,,,,,,,,,,
    10Y,11.68205606,6.630545933,2.763086374,1.224651527,0,-0.476853138,-1.333363294,-1.418332068,-0.942887045,,,,,,,,,,
  2. Adriaan New Member

    Use a temporary table. Insert all your partial results there, then at the end of your script just select from your temp table.
  3. satya Moderator

    Can you explain how and what you are trying to achieve using SSIS?

Share This Page