I have developed a report in Access 2010 that uses Excel tables as its data source. These Excel files are linked, as opposed to being embedded in the Access project. This report is a receipt, as from a point of sale. It has a report header, a detail section, and a report footer. The header and footer data comes from a single-record .xls file we'll call "A" and the sales lines detail from ...
I have a parameter of data group number in my report. Based on this parameter the report extracts respective information. For example, if 1 is selected in the parameter then company1's information is pulled. I want to pull the information and display all the three companies on separate page.
This way, when the report is exported to Excel, the header stuff and legend appear in the first sheet, while the report data appears in the second, separate sheet.
It is not the end purpose of the program, but the ability to load a cell value in to a variable, and a variable value in to a cell, using data from anywhere in the spreadsheet and manipulating the data and the spreadsheet will be a cakewalk.
I am using provider=Microsoft.ACE.OLEDB.12.0; for excel connector and running in 32 bit compatible mode in both SSDT and in SSISDB catalog. To single out the problem I created the package with only 1 dataflow task. Source is excel connection and destination in OLEDB destination connecting to local SQL Server 2012 database.
Pretty much just like a database call from .NET. Create connection statement using the OLDB driver, create select statement, execute the statement into datareader, use the data as desired by the app, close your datareader and connection.
I am facing problem with column data types. SSIS excel source is considering same column data type as string, sometimes as Unicode string and sometimes as Unicode Text stream based on underlying excel data. So what I want to do is to add dummy row which contains Unicode Text stream data and insert it to 1st row of excel using script component.
I have a Excel spreadsheet with around 500 names containing the name of the person and their email address etc. Apart from spending weeks, typing up an standard email, adding the specific names and emails from an excel spreadsheet, I have read this process can be automated using Outlook and Excel which would save me weeks.
