.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

newbie to SSIS - moving data from excel files with dynamic name

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :


I am a new to SSIS but not new to SQL server.  I have been used to programming stored procedures and such to get data in to SQL.  I need to daily import processes of excel files.  However, the name of the excel files change every day.  Is there anyway to dynamically handle this?



View Complete Post

More Related Resource Links

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

SSIS issue with dynamic excel sheet name

Hi Team, I am creating a Excel file dynamically using a script, excute sql and then the dataflow. I am not able to cretae a excel sheet name without an underscore exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing? I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question. Is this an issue with Sql server 2005? Thanks, Eshwar  

Creating Multiple Excel files Using SSIS

Hello Friends,  I have to create multiple excel files(Per Year eg 2008 , 2009, 2010....) from datatable using SSIS  For that i have to use one template excel file that is already created on some location.  Any help would be appreciated.  Thanks Thanks

Create dynamic dropdown list when we export data to excel using SSRS.


Hi All,

I have assigned a work to create a report, that should render in excel with dropdown list. i.e. report should contain a input parameter as a table names, when user selects the table name, report should show only the field names of the selected table. Some of the field name have look up values that values should be displayed as dropdown when we export to excel.

Can any one have any idea how to proceed with this report?



ssis how should i load data from excel to Xml destination



could any one tell me how could i load data from EXCEl file to XML
if some one could provide with appropriate link then it will b  a great help

i have  taken Excel source part is completed ..but what to do after that..


Calling Stored Procured in SSIS from database moving data into another Database




I am just wondering if this is a feasible option within SSIS.

I have a source database on one server, a destination database on another server

The source database has a stored procedure which returns lets say 500 records. 300 are inserts the rest updates. (Based on some column)

So in SSIS I call this SP, following this im able to loop through the records returned figure out wether its Insert or Update and then move the data to destination table.



Create Excel file dynamically in SSIS package and fill it by resultset data returned by Stored proce


Hello Every One,

I have a scenario in which i am executing a Stored Procedure which returned a result set.

I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].

Point is that the excel file should have unique name so i want to give name as name_date_time.

So every time when package run new file is created with unique name.


Can any one suggest me who to create Excel file and fill result set data into Excel file.

And then finally send that Excel file ?







When dynamically creating an excel file, can SSIS create xlsx files successfully? I can only get SSI


Problem: An Excel file (XLSX) dynamically created by a SQL Task cannot be opened via Excel. The task succeeds, but when you try to open the workbook in Excel you get the fillowing error message: Excel cannot open the file 'myfile.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

1. If the Excel file (xlsx) is created manually in Excel, then the SQL Task has no problem creating table (new worksheets) in the xlsx (so the "CREATE TABLE" statement works fine)
2. If I adjust the connection and task to work with Excel 97-2003 (xls) format then the approach works perfectly
3. The execution results show success of the SQL Task, but there are two warning messages. These same warnings appear when the SQL Task creates a new table (worksheet) successfully in a workbook (xlsx) which was created manually in Excel (and is not corrupt). Messages as follows:
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

SSIS Details:
1. ConnectionString: "Data Sour

Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?

SSIS import Excel with Data Connection


Hi guys, I am trying to use SSIS for the first time. What I am hoping to do is set up a scheduled job that will get SSIS to read an Excel spreadsheet on a regular basis and import the data out of it into an SQL Server database. Now my Excel spreadsheet uses a Data Connection to a different source and I want the data to be refreshed before SSIS imports it. Is that possible?

The problem I am facing right now is that the Data Connection does not seem to run before SSIS import the data. It keeps importing the old data that was sitting in the Excel spread sheet at the time it was saved last.

Is there any way I can tell SSIS to run the data connection first? Or is there something I need to do in my Excel file to ensure the connection is run?

By the way: the Excel file is set up to run the Data Connection immediately when it is opened normally in Excel, but the general user always first has to go into the Security Settings and enable Data Connections.

Thanks for the help!

Moving from Client Server to Dynamic Data


I am a Visual Basic 6 Client- Server's applications developer. I have followed the first steps to create a scaffolded site. It was easy and it works fine, roughly. Dynamic data seems very powerful but it just makes me believe that it could be an excellent tool for supporting development only. Any real experience deployed with it? I understand that ASP.Net is of the disconnected type but I feel a little lost when variables are not there? How do you manage state? D.D.

SSIS 2005 - Export Data into an Excel 2007 Table


Hello !

I have an excel file (xlsx) containing a table :

Excel Table

Once I launched my ssis task (successfully) to insert data in it, it is actually append after the table :

Excel Table after the SSIS task

So I am looking for a way to insert into the table and expand it with the data. I hope someone could help me.

Thank you !

Extracting data from multiple CSV files using SSIS package



I am new to SSIS. I am extracting data from multiple CSV files using SSIS package and copying that data into SQL server 2008  database. Can you please confirm if I can create only one package to handle all CSV files or I will have to create packages for each CSV file.

Column names and numbers are different in all CSV files. 







ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend