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 --============================================ 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,,,,,,,,,, lcerni

Question: Do the various sections of this output all have the same number of fields? Even the ones with just commas? If so, you could probably handle this with one Flat File Destination adapter that has String fields named Col1 through ColX and then have multiple data flows on the control Flow. each Data Flow would load one section into the flat file.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

i thing you can use Bulk Copy Command Line utility tool to export table data into text file based on your requirement... please refer the Bulk Copy usage..http://msdn.microsoft.com/en-us/library/ms191485.aspx -- Sathish

I would like to recommend to use script task to write this data into the text file. Have a execute SQL task to get the required output. For that as mentioned by Todd C , you should have the same number of columns on all the above batches.If not you have to have seperate execute SQL task for each and a script task to write the same into text file. For how to use script task to write to file ...check the below thread http://social.msdn.microsoft.com/Forums/en-NZ/sqlintegrationservices/thread/82639c70-1954-4c15-9f6e-2ece1c69ef78

Yes, the output for each each query should have the same number of columns. That is why all the commas at the end of the lines.lcerni

If all the columns are going to be same , you can make union of all the SELECT statement . follow the below steps 1. Add a data flow component to the package 2. Inside data flow component , add a OLEDB source component. 3. In the source component , change the data access mode to SQL Command. 4. In the SQL Command text box, put the UNION select statement. 5. Then add Flat file destination to the data flow area with the flatfile connection to the destination file. 6. Connect the source and the flat file detination components. Here is the altered select statment with UNION --============================================ DECLARE @ValuateDate DATETIME SET @ValuateDate = '2010-12-31 00:00:00.000' DECLARE @AsOfDate DATETIME SET @AsOfDate = '2010-12-31 00:00:00.000' DECLARE @yDate DATETIME SET @yDate = '2010-12-31 00:00:00.000' 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 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT CONVERT(VARCHAR, xdate, 112) + ',' + xperiod + ',' + CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,' FROM RC WHERE [Status] = 'A' AND xdate = @AsOfDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,' UNION ALL SELECT yperiod + ',' + CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,' FROM DY WHERE [Status] = 'A' AND yDate = @yDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT * FROM #TTXYZ UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' DROP TABLE #TTXYZ

Eror at Data Flow Task [Source - TTLJ[[1]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared." An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: )x80040E14 Desciption: "Invalid object name '#TTVC'" Additional information: Exception from HRESULT: 0xC0202009(Micorsoft.sqlserver.DTSPipelineWrap) --====================== This is the error that I get when I try to copy and paste my code to SQL Command and hit OK. How do I resolve this error? --====================== This is my code: DECLARE @ValuateDate DATETIME DECLARE @AsOfDate DATETIME DECLARE @DYDate DATETIME DECLARE @FileDate DATETIME DECLARE @Tenor3YP VARCHAR(5) DECLARE @Tenor5YP VARCHAR(5) DECLARE @Tenor10YP VARCHAR(5) DECLARE @Tenor20YP VARCHAR(5) DECLARE @Tenor30YP VARCHAR(5) SET @ValuateDate = '2011-04-25 00:00:00.000' SET @AsOfDate = @ValuateDate SET @DYDate = @ValuateDate SET @FileDate = @ValuateDate SET @Tenor3YP = '3Y/P' SET @Tenor5YP = '5Y/P' SET @Tenor10YP = '10Y/P' SET @Tenor20YP = '20Y/P' SET @Tenor30YP = '30Y/P' --============================================ ------------------ CREATE TABLE #TTVC (OutputVC VARCHAR(2000)) INSERT INTO #TTVC SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200' INSERT INTO #TTVC 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 VolatilityCurve 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] ------------------ CREATE TABLE #TTSC (OutputSC VARCHAR(2000)) INSERT INTO #TTSC SELECT 'TERMS' + ',' + '1Y' + ',' + '2Y' + ',' + '3Y' + ',' + '4Y' + ',' + '5Y' + ',' + '7Y' + ',' + '10Y' + ',' + '15Y' + ',' + '20Y' + ',' + '25Y' + ',' + '30Y' + ',,,,,,,,' INSERT INTO #TTSC SELECT CONVERT(VARCHAR(250), A.Term )+ ',' + CONVERT(VARCHAR(250), A.[1Y]) + ',' + CONVERT(VARCHAR(250), A.[2Y]) + ',' + CONVERT(VARCHAR(250), A.[3Y]) + ',' + CONVERT(VARCHAR(250), A.[4Y]) + ',' + CONVERT(VARCHAR(250), A.[5Y]) + ',' + CONVERT(VARCHAR(250), A.[7Y]) + ',' + CONVERT(VARCHAR(250), A.[10Y]) + ',' + CONVERT(VARCHAR(250), A.[15Y]) + ',' + CONVERT(VARCHAR(250), A.[20Y]) + ',' + CONVERT(VARCHAR(250), A.[25Y]) + ',' + CONVERT(VARCHAR(250), A.[30Y]) + ',,,,,,,,' FROM ( SELECT Term, [1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurve WHERE [Status] = 'A' AND FileDate=@FileDate ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y]) ) 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 '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 '7Y', 11 UNION All SELECT '10Y', 12 UNION All SELECT '15Y', 13 UNION All SELECT '20Y', 14 UNION All SELECT '25Y', 13 UNION All SELECT '30Y', 14 ) B WHERE A.Term = B.Term ORDER BY [Order] ------------------ CREATE TABLE #TT3YP (Output3YP VARCHAR(2000)) INSERT INTO #TT3YP SELECT @Tenor3YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO #TT3YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurveTenor WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor3YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ CREATE TABLE #TT5YP (Output5YP VARCHAR(2000)) INSERT INTO #TT5YP SELECT @Tenor5YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO #TT5YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurveTenor WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor5YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ CREATE TABLE #TT10YP (Output10YP VARCHAR(2000)) INSERT INTO #TT10YP SELECT @Tenor10YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO #TT10YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurveTenor WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor10YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ CREATE TABLE #TT20YP (Output20YP VARCHAR(2000)) INSERT INTO #TT20YP SELECT @Tenor20YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO #TT20YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurveTenor WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor20YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ CREATE TABLE #TT30YP (Output30YP VARCHAR(2000)) INSERT INTO #TT30YP SELECT @Tenor30YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO #TT30YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SwaptionCurveTenor WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor30YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT CONVERT(VARCHAR, rydate, 112) + ',' + ryperiod + ',' + CONVERT(VARCHAR(250), rymidrate) + ',,,,,,,,,,,,,,,,,' FROM RiskYieldCurve WHERE [Status] = 'A' AND rydate = @AsOfDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,' UNION ALL SELECT dyperiod + ',' + CONVERT(VARCHAR(250), dymidrate) + ',,,,,,,,,,,,,,,,,,' FROM DividendYield WHERE [Status] = 'A' AND DYDate = @DYDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TTVC UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TTSC UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TT3YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TT5YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TT10YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TT20YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM #TT30YP --============================================ DROP TABLE #TT3YP DROP TABLE #TT5YP DROP TABLE #TT10YP DROP TABLE #TT20YP DROP TABLE #TT30YP DROP TABLE #TTVC DROP TABLE #TTSC --============================================lcerni

lcerni: You REALLY need to break this up into manageable portions. AND temp tables (#TT3YP) don't work in Execute SQL Tasks. They might work OK in Source Adapters, but really, I would get this to work with ONE section at a time.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

So lets say I work with the first two queries. In the DataFlow I will have One Source - Query1 which links to Destination - Text1 for the first query. Then I would have a second Source - Query2 which links to a Destination - Text2. How do I append the results of the second query to the same text file without overriding the output from the first query? Do I need to link Destination - Text1 to Source - Query2 so that it runs sequentially?lcerni

Yes, that's the basic idea. See if you can get two Data flows to run, one right after another, that both write to the same text file destination, AND see if you can make it so that the second DFT (and subsequent ones) do NOT overwrite existing data. If you can get that simple scenario to work, you're half way there. Just need to add the appropriate additional Data Flow Tasks that all dump to the same destination. If you CAN'T get that to work, then you might try breaking the secitons out onto one Data Flow, with each secition being its own Source Adapter, and then combine them in a series of Union All tasks. (I think this was suggested somewhere along the line here.) good luck!Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I cannot get nothing to work. Any other ideas out there.lcerni

I cannot get nothing to work. Any other ideas out there. lcerni Hi, Can you give me more info about your package? or you still didn't create one? ThanksMCITP - BI 2008 http://asqlb.blogspot.com/

Convert the #temp table to a table variable. Also Make sure to add a SET NOCOUNT ON at the starting of the script. Try the below one --============================================ SET NOCOUNT ON DECLARE @ValuateDate DATETIME SET @ValuateDate = '2010-12-31 00:00:00.000' DECLARE @AsOfDate DATETIME SET @AsOfDate = '2010-12-31 00:00:00.000' DECLARE @yDate DATETIME SET @yDate = '2010-12-31 00:00:00.000' DECLARE @TTXYZ TABLE (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 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,' UNION ALL SELECT CONVERT(VARCHAR, xdate, 112) + ',' + xperiod + ',' + CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,' FROM RC WHERE [Status] = 'A' AND xdate = @AsOfDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,' UNION ALL SELECT yperiod + ',' + CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,' FROM DY WHERE [Status] = 'A' AND yDate = @yDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' UNION ALL SELECT * FROM @TTXYZ UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' SET NOCOUNT OFF

Under Control Flow created Data Flow Task. Under Data Flow created OLE DB Source and Flat File Destination Tasks. Set up OLE DB Source Editor with Data access mode as "SQL command from variable" Variable name as User::ExecuteNameHere Under Variables set up Variable ExecuteInput with DataType as String and Value as Execute ProcedureNameHere ParameterHere Set EvaluateAsExpression as True Set Expression as "EXECUTE ProcedureNameHere '" + (DT_WSTR, 10) @[User::EvaluationDate] + "'" CREATE PROCEDURE [dbo].[ProcedureNameHere] @EvaluationDate DATETIME AS BEGIN SET NOCOUNT ON --DECLARE @ValuateDate DATETIME DECLARE @AsOfDate DATETIME DECLARE @DYDate DATETIME DECLARE @FileDate DATETIME DECLARE @Tenor3YP VARCHAR(5) DECLARE @Tenor5YP VARCHAR(5) DECLARE @Tenor10YP VARCHAR(5) DECLARE @Tenor20YP VARCHAR(5) DECLARE @Tenor30YP VARCHAR(5) --SET @ValuateDate = '2011-04-25 00:00:00.000' SET @AsOfDate = @ValuateDate SET @DYDate = @ValuateDate SET @FileDate = @ValuateDate SET @Tenor3YP = '3Y/P' SET @Tenor5YP = '5Y/P' SET @Tenor10YP = '10Y/P' SET @Tenor20YP = '20Y/P' SET @Tenor30YP = '30Y/P' --============================================ --NEED TO CREATE TEMP TABLES BECAUSE OF ORDER BY ------------------ DECLARE @TTVC TABLE (OutputVC VARCHAR(500)) INSERT INTO @TTVC SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200' INSERT INTO @TTVC 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 VC 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] ------------------ DECLARE @TTSC TABLE (OutputSC VARCHAR(500)) INSERT INTO @TTSC SELECT 'TERMS' + ',' + '1Y' + ',' + '2Y' + ',' + '3Y' + ',' + '4Y' + ',' + '5Y' + ',' + '7Y' + ',' + '10Y' + ',' + '15Y' + ',' + '20Y' + ',' + '25Y' + ',' + '30Y' + ',,,,,,,,' INSERT INTO @TTSC SELECT CONVERT(VARCHAR(250), A.Term )+ ',' + CONVERT(VARCHAR(250), A.[1Y]) + ',' + CONVERT(VARCHAR(250), A.[2Y]) + ',' + CONVERT(VARCHAR(250), A.[3Y]) + ',' + CONVERT(VARCHAR(250), A.[4Y]) + ',' + CONVERT(VARCHAR(250), A.[5Y]) + ',' + CONVERT(VARCHAR(250), A.[7Y]) + ',' + CONVERT(VARCHAR(250), A.[10Y]) + ',' + CONVERT(VARCHAR(250), A.[15Y]) + ',' + CONVERT(VARCHAR(250), A.[20Y]) + ',' + CONVERT(VARCHAR(250), A.[25Y]) + ',' + CONVERT(VARCHAR(250), A.[30Y]) + ',,,,,,,,' FROM ( SELECT Term, [1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y] FROM (SELECT Term, Percentile, Strike FROM SC WHERE [Status] = 'A' AND FileDate=@FileDate ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y]) ) 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 '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 '7Y', 11 UNION All SELECT '10Y', 12 UNION All SELECT '15Y', 13 UNION All SELECT '20Y', 14 UNION All SELECT '25Y', 13 UNION All SELECT '30Y', 14 ) B WHERE A.Term = B.Term ORDER BY [Order] ------------------ DECLARE @TT3YP TABLE (Output3YP VARCHAR(500)) INSERT INTO @TT3YP SELECT @Tenor3YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO @TT3YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SCT WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor3YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ DECLARE @TT5YP TABLE (Output5YP VARCHAR(500)) INSERT INTO @TT5YP SELECT @Tenor5YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO @TT5YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SCT WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor5YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ DECLARE @TT10YP TABLE (Output10YP VARCHAR(500)) INSERT INTO @TT10YP SELECT @Tenor10YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO @TT10YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SCT WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor10YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ DECLARE @TT20YP TABLE (Output20YP VARCHAR(500)) INSERT INTO @TT20YP SELECT @Tenor20YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO @TT20YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SCT WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor20YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ DECLARE @TT30YP TABLE (Output30YP VARCHAR(500)) INSERT INTO @TT30YP SELECT @Tenor30YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,' INSERT INTO @TT30YP SELECT CONVERT(VARCHAR(250), A.Term ) + ',' + CONVERT(VARCHAR(250), A.[-300]) + ',' + CONVERT(VARCHAR(250), A.[-200]) + ',' + CONVERT(VARCHAR(250), A.[-100]) + ',' + CONVERT(VARCHAR(250), A.[-50]) + ',' + CONVERT(VARCHAR(250), A.[0]) + ',' + CONVERT(VARCHAR(250), A.[50]) + ',' + CONVERT(VARCHAR(250), A.[100]) + ',' + CONVERT(VARCHAR(250), A.[200]) + ',' + CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,' FROM ( SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300] FROM (SELECT Term, Percentile, Strike FROM SCT WHERE [Status] = 'A' AND FileDate = @FileDate AND Tenor = @Tenor30YP ) ps PIVOT ( SUM (Strike) FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]) ) AS pvt ) A, ( SELECT 'Term' 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] ------------------ --============================================ DECLARE @TTLJC TABLE (Col1 VARCHAR(500)) INSERT INTO @TTLJC -- SELECT 'Col1' --UNION ALL SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,' AS Col1 UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' AS Col1 --============================================ UNION ALL SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,' AS Col1 UNION ALL SELECT CONVERT(VARCHAR, rydate, 112) + ',' + ryperiod + ',' + CONVERT(VARCHAR(250), rymidrate) + ',,,,,,,,,,,,,,,,,' FROM RiskYieldCurve WHERE [Status] = 'A' AND rydate = @AsOfDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,' UNION ALL SELECT dyperiod + ',' + CONVERT(VARCHAR(250), dymidrate) + ',,,,,,,,,,,,,,,,,,' FROM DividendYield WHERE [Status] = 'A' AND DYDate = @DYDate UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TTVC --DOING IT THIS WAY BECAUSE OF ORDER BY -- SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200' -- UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TTSC UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TT3YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TT5YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TT10YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TT20YP UNION ALL SELECT ',,,,,,,,,,,,,,,,,,,' --============================================ UNION ALL SELECT * FROM @TT30YP --============================================ SELECT CAST(Col1 AS NVARCHAR(500)) AS Col1 FROM @TTLJC --============================================ --============================================ ENDlcerni