Home
%3CLINGO-SUB%20id%3D%22lingo-sub-789703%22%20slang%3D%22en-US%22%3EUse%20SSIS%20to%20load%20data%20in%20chunks%20from%20SQLServer%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789703%22%20slang%3D%22en-US%22%3E%3C!--%20%5Bif%20!mso%5D%3E%0A%3Cstyle%3E%0Av%5C%3A*%20%7Bbehavior%3Aurl(%23default%23VML)%3B%7D%0Ao%5C%3A*%20%7Bbehavior%3Aurl(%23default%23VML)%3B%7D%0Aw%5C%3A*%20%7Bbehavior%3Aurl(%23default%23VML)%3B%7D%0A.shape%20%7Bbehavior%3Aurl(%23default%23VML)%3B%7D%0A%3C%2Fstyle%3E%0A%3C!%5Bendif%5D--%3E%3C!--%20%5Bif%20gte%20mso%209%5D%3E%3Cxml%3E%0A%20%3Co%3ADocumentProperties%3E%0A%20%20%3Co%3AAuthor%3EQianwen%20Shi%3C%2Fo%3AAuthor%3E%0A%20%20%3Co%3ATemplate%3ENormal%3C%2Fo%3ATemplate%3E%0A%20%20%3Co%3ALastAuthor%3EQianwen%20Shi%3C%2Fo%3ALastAuthor%3E%0A%20%20%3Co%3ARevision%3E2%3C%2Fo%3ARevision%3E%0A%20%20%3Co%3ATotalTime%3E51%3C%2Fo%3ATotalTime%3E%0A%20%20%3Co%3ACreated%3E2019-08-06T08%3A56%3A00Z%3C%2Fo%3ACreated%3E%0A%20%20%3Co%3ALastSaved%3E2019-08-06T08%3A56%3A00Z%3C%2Fo%3ALastSaved%3E%0A%20%20%3Co%3APages%3E7%3C%2Fo%3APages%3E%0A%20%20%3Co%3AWords%3E178%3C%2Fo%3AWords%3E%0A%20%20%3Co%3ACharacters%3E1016%3C%2Fo%3ACharacters%3E%0A%20%20%3Co%3ALines%3E8%3C%2Fo%3ALines%3E%0A%20%20%3Co%3AParagraphs%3E2%3C%2Fo%3AParagraphs%3E%0A%20%20%3Co%3ACharactersWithSpaces%3E1192%3C%2Fo%3ACharactersWithSpaces%3E%0A%20%20%3Co%3AVersion%3E16.00%3C%2Fo%3AVersion%3E%0A%20%3C%2Fo%3ADocumentProperties%3E%0A%20%3Co%3ACustomDocumentProperties%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Enabled%20dt%3Adt%3D%22string%22%3ETrue%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Enabled%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SiteId%20dt%3Adt%3D%22string%22%3E72f988bf-86f1-41af-91ab-2d7cd011db47%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SiteId%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Owner%20dt%3Adt%3D%22string%22%3Eqianwens%40microsoft.com%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Owner%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SetDate%20dt%3Adt%3D%22string%22%3E2019-08-02T12%3A46%3A02.0171492Z%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SetDate%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Name%20dt%3Adt%3D%22string%22%3EGeneral%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Name%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Application%20dt%3Adt%3D%22string%22%3EMicrosoft%20Azure%20Information%20Protection%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Application%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_ActionId%20dt%3Adt%3D%22string%22%3Ed8c973c5-5a3c-4268-b22e-b0860d8af32e%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_ActionId%3E%0A%20%20%3Co%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Extended_MSFT_Method%0A%20%20%20dt%3Adt%3D%22string%22%3EAutomatic%3C%2Fo%3AMSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Extended_MSFT_Method%3E%0A%20%20%3Co%3ASensitivity%20dt%3Adt%3D%22string%22%3EGeneral%3C%2Fo%3ASensitivity%3E%0A%20%3C%2Fo%3ACustomDocumentProperties%3E%0A%20%3Co%3AOfficeDocumentSettings%3E%0A%20%20%3Co%3AAllowPNG%2F%3E%0A%20%3C%2Fo%3AOfficeDocumentSettings%3E%0A%3C%2Fxml%3E%3C!%5Bendif%5D--%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EUse%20SSIS%20to%20load%20data%20in%20chunks%20to%20Excel%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThis%20sample%20SSIS%20package%20has%20below%20steps%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E1.%20Read%20row%20count%20of%20the%20data%20to%20load%20from%20SQL%20Server%20and%20save%20the%20row%20count%20in%20variable%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E2.%20Split%20the%20rows%20to%20n%20chunks%20in%20a%20For%20Loop%20Container%2C%20create%20an%20Excel%20sheet%20for%20each%20chunk%20and%20load%20data%20into%20it%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EPackage%20Control%20Flow%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E1.%20Create%20a%20Execute%20SQL%20Task%20to%20read%20row%20count%20from%20SQL%20Server%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E2.%20Create%20a%20For%20Loop%20Container%20to%20split%20the%20row%20count%20to%20n%20chunks%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E3.%20Create%20a%20Execute%20SQL%20Task%20in%20For%20Loop%20Container%20to%20create%20Excel%20sheet%20for%20each%20chunk%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E4.%20Create%20a%20Data%20Flow%20Task%20In%20For%20Loop%20Container%20to%20load%20chunk%20data%20to%20Excel%20sheet%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20490px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125722i821656AC655C1EE3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22package.JPG%22%20title%3D%22package.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EBelow%20document%20describe%20the%20details%20for%20each%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Ecomponent%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EVariables%20in%20package%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ECreate%20variables%20in%20package%20as%20below%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125723i1C43AE3629E18516%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22variable.JPG%22%20title%3D%22variable.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EExecute%20SQL%20Task%20%E2%80%93%20Read%20Row%20Count%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20%3CSPAN%20class%3D%22SpellE%22%3ESQLStatement%3C%2FSPAN%3E%3A%20select%20%3CSPAN%20class%3D%22GramE%22%3Ecount(%3C%2FSPAN%3E*)%20as%20count%20from%20dbo.int%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20732px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125724i88DC8846DA1B16B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.JPG%22%20title%3D%221.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EMap%20the%20result%20of%20count%20to%20variable%20%3CSPAN%20class%3D%22GramE%22%3EUser%3A%3A%3C%2FSPAN%3E%3CSPAN%20class%3D%22SpellE%22%3Erowcount%3C%2FSPAN%3Ein%20Result%20Set%20page%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20725px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125726iB23021DCE1AFC32C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222.JPG%22%20title%3D%222.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EFor%20Loop%20Container%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20%3CSPAN%20class%3D%22SpellE%22%3EInitiExpression%3C%2FSPAN%3E%3A%20%40iterator%20%3D%200%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20%3CSPAN%20class%3D%22SpellE%22%3EEvalExpression%3C%2FSPAN%3E%3A%20%40iterator*%40%40%3CSPAN%20class%3D%22SpellE%22%3Echunksize%3C%2FSPAN%3E%26lt%3B%20%40%3CSPAN%20class%3D%22SpellE%22%3Erowcount%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20%3CSPAN%20class%3D%22SpellE%22%3EAssignExpression%3C%2FSPAN%3E%3A%20%40iterator%20%3D%20%40iterator%20%2B%201%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20727px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125728i66420E2F2D3AB4AF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22loop.JPG%22%20title%3D%22loop.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EExecute%20SQL%20Task%20-%20Create%20Excel%20Sheet%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESelect%20Excel%20in%20%3CSPAN%20class%3D%22SpellE%22%3EConnectionType%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20%3CSPAN%20class%3D%22SpellE%22%3ESQLStatement%3C%2FSPAN%3Ein%20Expressions%3A%20%22CREATE%20TABLE%20%60%22%20%2B%20%40%5B%3CSPAN%20class%3D%22GramE%22%3EUser%3A%3A%3C%2FSPAN%3E%3CSPAN%20class%3D%22SpellE%22%3EsheetName%3C%2FSPAN%3E%5D%20%2B%20%22%60%20(%20%60column1%60%20INTEGER)%22%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20844px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125729iF9C6F01ADC60EE7E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%223.JPG%22%20title%3D%223.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EData%20Flow%20Task%20-%20Load%20Data%20%3CSPAN%20class%3D%22GramE%22%3ETo%3C%2FSPAN%3EExcel%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ECreate%20ADO%20NET%20Source%20component%20to%20load%20data%20from%20SQL%20Server%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ECreate%20Excel%20Destination%20component%20to%20load%20data%20to%20Excel%20table%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ESet%20the%20DelayValidation%20property%20of%20this%20data%20flow%20task%20to%20%22True%22%20to%20bypass%20the%20validation%20before%20execution%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20363px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125730i5CCBE735328CE4B4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%224.JPG%22%20title%3D%224.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EIn%20Expressions%20of%20this%20data%20flow%20task%2C%20set%20%5BADO%20NET%20Source%3CSPAN%20class%3D%22GramE%22%3E%5D.%5B%3C%2FSPAN%3E%3CSPAN%20class%3D%22SpellE%22%3ESqlCommand%3C%2FSPAN%3E%5D%20%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%22select%20*%20from%20dbo.int%20order%20by%20a%20OFFSET%20%22%20%2B%20(DT_WSTR%2C%2010)%20(%40%5BUser%3A%3Aiterator%5D%20*%20%40%5BUser%3A%3A%3CSPAN%20class%3D%22SpellE%22%3Echunksize%3C%2FSPAN%3E%5D)%20%2B%20%22%20ROWS%20FETCH%20NEXT%20%22%20%2B%20(DT_WSTR%2C%2010)%20%40%5BUser%3A%3A%3CSPAN%20class%3D%22SpellE%22%3Echunksize%3C%2FSPAN%3E%5D%20%2B%20%22%20ROWS%20ONLY%22%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20936px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125731iECA0F2DB63127DBA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%225.JPG%22%20title%3D%225.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EIn%20Excel%20Destination%2C%20create%20a%20Excel%20connection%20manager%20for%20target%20Excel%20file%2C%20set%20the%20table%20name%20with%20variable%20%3CSPAN%20class%3D%22SpellE%22%3E%3CSPAN%20class%3D%22GramE%22%3EUser%3AexcelTableName%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20814px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125732i6B5F86E77ED543D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%226.JPG%22%20title%3D%226.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EExecute%20this%20package%20will%20dynamically%20create%20sheets%20in%20the%20Excel%20file.%20It%20will%20fail%20to%20execute%20if%20the%20sheets%20already%20exists.%20You%20can%20update%20the%20expression%20to%20include%20TimeStamp%20in%20the%20Excel%20sheet%20name%20to%20create%20new%20sheets%20for%20each%20execution.%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-789703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20490px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125721iE643C8102A2B4C18%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22package.JPG%22%20title%3D%22package.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThis%20blog%20provides%20a%20solution%20to%20use%26nbsp%3BSSIS%20to%20load%20data%20in%20chunks%20from%20SQLServer%20to%20Excel.%20The%20sample%20package%20has%20below%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ERead%20row%20count%20of%20the%20data%20to%20load%20from%20SQL%20Server%20and%20save%20the%20row%20count%20in%20variable%3C%2FLI%3E%0A%3CLI%3ESplit%20the%20rows%20to%20n%20chunks%20in%20a%20For%20Loop%20Container%2C%20create%20an%20Excel%20sheet%20for%20each%20chunk%20and%20load%20data%20in%20each%20chunk%20to%20Excel%20sheet%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-789703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Echunk%20data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Essis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Use SSIS to load data in chunks to Excel

This sample SSIS package has below steps:

1. Read row count of the data to load from SQL Server and save the row count in variable

2. Split the rows to n chunks in a For Loop Container, create an Excel sheet for each chunk and load data into it

Package Control Flow:

1. Create a Execute SQL Task to read row count from SQL Server

2. Create a For Loop Container to split the row count to n chunks 

3. Create a Execute SQL Task in For Loop Container to create Excel sheet for each chunk

4. Create a Data Flow Task In For Loop Container to load chunk data to Excel sheet

package.JPG

 

Below document describe the details for each component

Variables in package:

Create variables in package as below

variable.JPG

Execute SQL Task – Read Row Count

Set SQLStatement: select count(*) as count from dbo.int

 

1.JPG

Map the result of count to variable User::rowcount in Result Set page

2.JPG

For Loop Container:

Set InitiExpression: @iterator = 0

Set EvalExpression: @iterator*@@chunksize < @rowcount

Set AssignExpression: @iterator = @iterator + 1

loop.JPG

Execute SQL Task - Create Excel Sheet

Select Excel in ConnectionType

Set SQLStatement in Expressions: "CREATE TABLE `" + @[User::sheetName] + "` ( `column1` INTEGER)"

3.JPG

Data Flow Task - Load Data To Excel

Create ADO NET Source component to load data from SQL Server

Create Excel Destination component to load data to Excel table

Set the DelayValidation property of this data flow task to "True" to bypass the validation before execution

4.JPG

In Expressions of this data flow task, set [ADO NET Source].[SqlCommand] :

"select * from dbo.int order by a OFFSET " + (DT_WSTR, 10) (@[User::iterator] * @[User::chunksize]) + " ROWS FETCH NEXT " + (DT_WSTR, 10) @[User::chunksize] + " ROWS ONLY"

5.JPG

In Excel Destination, create a Excel connection manager for target Excel file, set the table name with variable User:excelTableName

6.JPG

 

Execute this package will dynamically create sheets in the Excel file. It will fail to execute if the sheets already exists. You can update the expression to include TimeStamp in the Excel sheet name to create new sheets for each execution.