Integration Services and Excel Tips

Recently I worked on a project to import Excel spreadsheets from various vendors into our database and not all spreadsheets had the same row number for the header record. This is because most of the vendors had additional information on the top of the header row. In this tip I am going to show how the SSIS script task can be used to solve this issue.

One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet. One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode. SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space. Is there any simple way to do this in SSIS?

We use SSIS to periodically load data into our data warehouse. While much of the data we process is in relational data stores, we do have some Excel spreadsheets that we need to process. In one particular case we load an Excel spreadsheet that is produced by an external application and every month the number of sheets varies and the sheet names are also different. How can we determine the sheet names in our SSIS package and process this variable number of sheets?

Recently I needed to find a method to import one cell of an Excel sheet into SQL Server 2005 using a scheduled job on a 64 bit clustered environment. For example, I have a monthly sales report listing sales per product category, such as Books, Magazines, DVDs, etc. I only want to import the Total Sales amount into SQL Server and this number is located in an Excel file "sales.xls.sheet1.cell.B5". I used to use OPENROWSET to solve the problem, however OPENROWSET requires Microsoft Jet OLE DB Provider which is not available in 64 bit. In this tip I am going to talk about how to use SSIS to accomplish this task easily.

One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet. One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode. SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space. Is there any simple way to do this in SSIS?

We use SSIS to periodically load data into our data warehouse. While much of the data we process is in relational data stores, we do have some Excel spreadsheets that we need to process. In one particular case we load an Excel spreadsheet that is produced by an external application and every month the number of sheets varies and the sheet names are also different. How can we determine the sheet names in our SSIS package and process this variable number of sheets?

Recently I needed to find a method to import one cell of an Excel sheet into SQL Server 2005 using a scheduled job on a 64 bit clustered environment. For example, I have a monthly sales report listing sales per product category, such as Books, Magazines, DVDs, etc. I only want to import the Total Sales amount into SQL Server and this number is located in an Excel file "sales.xls.sheet1.cell.B5". I used to use OPENROWSET to solve the problem, however OPENROWSET requires Microsoft Jet OLE DB Provider which is not available in 64 bit. In this tip I am going to talk about how to use SSIS to accomplish this task easily.

Recently I worked on a project to import Excel spreadsheets from various vendors into our database and not all spreadsheets had the same row number for the header record. This is because most of the vendors had additional information on the top of the header row. In this tip I am going to show how the SSIS script task can be used to solve this issue.

Recently I worked on a project to import Excel spreadsheets from various vendors into our database and not all spreadsheets had the same row number for the header record. This is because most of the vendors had additional information on the top of the header row. In this tip I am going to show how the SSIS script task can be used to solve this issue.

Recently I needed to find a method to import one cell of an Excel sheet into SQL Server 2005 using a scheduled job on a 64 bit clustered environment. For example, I have a monthly sales report listing sales per product category, such as Books, Magazines, DVDs, etc. I only want to import the Total Sales amount into SQL Server and this number is located in an Excel file "sales.xls.sheet1.cell.B5". I used to use OPENROWSET to solve the problem, however OPENROWSET requires Microsoft Jet OLE DB Provider which is not available in 64 bit. In this tip I am going to talk about how to use SSIS to accomplish this task easily.

We use SSIS to periodically load data into our data warehouse. While much of the data we process is in relational data stores, we do have some Excel spreadsheets that we need to process. In one particular case we load an Excel spreadsheet that is produced by an external application and every month the number of sheets varies and the sheet names are also different. How can we determine the sheet names in our SSIS package and process this variable number of sheets?

One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet. One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode. SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space. Is there any simple way to do this in SSIS?