none
Is there a way to use .CSV or Excel template for generating multiple dynamic files using SSIS package?

    Question

  • Hi,

    I'm able to generate .csv files dynamically using SSIS package. However, my requirement is to use an existing excel template so that way it has nice colors on the column headers. I searched online number of times but no luck.

    Let me ask this.. Is it possible to use template in SSIS for dynamic files at all?

    I know we can use it for a single file.

    Thanks in advance

    Kesav 

    Friday, October 18, 2013 1:53 PM

Answers

All replies

  • It is possible if you set the Excel connection to not to override the destination.

    So you get your Excel file formatted and then fill it with data, you can even issue SQL update queries to it which makes the population process quite well controlled.

    E.g.

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
    SELECT Name, Email FROM tblnames

    see http://geekswithblogs.net/nagendraprasad/archive/2009/03/26/export-sql-server-data-into-excel.aspx


    Arthur My Blog

    Friday, October 18, 2013 2:59 PM
  • I don't see any option that set the Excel connection to not to override the destination.

    Can you please elaborate more on this solution? Do you think it is possible writing script? Any information would be great.

    Let me explain about my package:  Execute SQL- passes Start Date and End Dates to the Data Flow Stored Procedure as a two input parameters.

    FOR EACH LOOP- It will loop through 13 times for different date parameters.

    DATA FLOW: OLEDB Datasource, Flat File Destination

    So my output files are .CSV files (but I can also use .Excel if there is an solution).

    In my flat file connection manager, I set the expression to dynamically generate files based on the date parameters.

    "C:\\XXX\\XXX" +" _"+(DT_STR, 4, 1252) DATEPART("yy" , @[User::START_DT]) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , @[User::START_DT]), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::START_DT]), 2) +" _ "+ (DT_STR, 4, 1252) DATEPART("yy" , @[User::END_DT] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , @[User::END_DT]), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::END_DT] ), 2) +" _ "+(DT_STR,4,1252) DatePart("yyyy",getdate()) +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)+ ".csv"

    Output files look like this:

    1)20120101_20130101.csv

    2)20120201_20130201.csv   3), 4), etc.




    Friday, October 18, 2013 5:33 PM
  • Yes, it is similar

    You can start with http://brandietarvin.livejournal.com/26681.html then hop to http://www.brandietarvin.com/2011/04/28/sql-server/blog-using-excel-templates-in-ssis-sqlserver/


    Arthur My Blog

    Friday, October 18, 2013 5:53 PM
  • isnt it just a matter of using File System Task to copy your template from a path, then fill it with data and finally rename it using your expression?
    Friday, October 18, 2013 6:10 PM
  • I followed the same steps and thought it will work but ended up getting error in my Excel Destination.

    Even I used  Data access mode as Table name or view name variable and Variable name as DestinationPath

    Error at Data Flow Task [Excel Destination [1143]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.

    Error at Data Flow Task [Excel Destination [1143]]: Opening a rowset for "C:\DataFiles\18991230 _ 18991230 _ 20131018.xlsx" failed. Check that the object exists in the database.

     (Microsoft Visual Studio)

    ===================================

    Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
    Friday, October 18, 2013 7:56 PM
  • Error Opening a rowset for "C:\DataFiles\18991230 _ 18991230 _ 20131018.xlsx" failed. Check that the object exists in the database

    means there was no file present at runtime or no access


    Arthur My Blog

    Friday, October 18, 2013 8:45 PM
  • I am still working on this but i have a quick question on .CSV files. Is it possible for using .CSF file instead .XLS files?

    I'm trying to format with column headers and saving the file, I keep getting error message and if click Yes then it will not save the formatted changes.

    So .CSF files can't be formatted?

    THanks in advance.

    Monday, October 21, 2013 2:03 PM
  • Finally, I got it working. I will say this.. without your help I can't imagine doing it alone. 

    Errors I was getting at the end are related to my package configurations. I had to change the project properties -> Debugging-> Run64bit =FALSE. That resolved the problem.

    Another question is: My template excel file is keep on filling it with all the information that is generating from the different files. Let's say my SSIS package dynamically generating 10 files into a folder. All the 10 files information is appending in the template file. Is there any way that I can open up and delete DATA only from temp file each time?

    THanks

    Monday, October 21, 2013 7:11 PM
  • I am not sure what is going on, are you generating files and then pump the data from each to yet another file ( the temp file)?

    Arthur My Blog

    Monday, October 21, 2013 7:17 PM
  • I think we have not set the DelayValidation property to True. This is required to have dynamic name at the run-time.

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Monday, October 21, 2013 8:07 PM
  • Wow I don't see any issue now. I'm not able to reproduce the same error. But I think what extra step I did compare to before was, I set DelayValidation set to True by clicking Data Flow Task.

    So I'm all set now. Please let me know if any one come across this type of situation. I can explain.

    THanks.

    Please set the flag to RESOLVED.

    Again, Thank you all for the help!!!!!!!!! 


    Monday, October 21, 2013 10:19 PM