InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 01:53:17 GMT20http://www.sqlservercentral.com/Forums/Topic724773-148-1.aspx[quote][b]Munabhai (11/8/2012)[/b][hr]Hi I have same problem, I could not follow you could you please explan little more detail what to do.It would be really great help, I spend, 5 hrs on this. In .cvs works perfect[/quote]What's the issue? Any errors?Fri, 09 Nov 2012 00:30:52 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic724773-148-1.aspxHi I have same problem, I could not follow you could you please explan little more detail what to do.It would be really great help, I spend, 5 hrs on this. In .cvs works perfectThu, 08 Nov 2012 16:31:18 GMTMunabhaihttp://www.sqlservercentral.com/Forums/Topic724773-148-1.aspxThanks for the reply Phil! I completely rebuilt the package, set delay validation = false..and it runs without error! One more question. I'm trying to execute the package from a SP. What's the proper syntax to pass the variable from the SP to the SSIS package? My overall goal is to call the SSIS package programatically from an asp.net application. So I'll be passing the filename as a varaible from the program. Here's what I have so far: ASDECLARE @cmd varchar(1000)DECLARE @SSISpath varchar(1000)DECLARE @ReturnCode int set @SSISpath = 'F:\apps\SSIS Packages\ExcelImport.dtsx' set @cmd= 'dtexec /F "' + @SSISpath + '"' set @cmd = @cmd + ' /SET \Package.Variables[User::FileName].Properties[Value];"' EXEC @ReturnCode = master..xp_cmdshell @cmd Again, thanks for all your help!Wed, 27 May 2009 23:24:14 GMTjbsaarihttp://www.sqlservercentral.com/Forums/Topic724773-148-1.aspxDelay validation should be set to True, not False, if the source file does not exist until run-time.You could approach this a different way. Set up a hard-coded connection to, say, Data.xls and as the first part in your package, copy the dynamically named Excel file to it, overwriting what is already there.Then allow the process to run using data.xls, which will always exist and your validation problems should go away.PhilWed, 27 May 2009 22:24:20 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic724773-148-1.aspxWhat are the steps necessary to pass the Excel Source the excel file name as a variable? I've tried this a few different ways now and have had issues each way. I've been trying to get this working for days! I've read a couple of long threads that involve a ForEach Loop Container to get multiple excel files and I've stepped thru them and am still stuck. I only wish to load one file per run. The format and location of the Excel File will never change - just the file name. Here's what I've tried most recently: 1. Create VariableVariable Name: FileNameScope: Package Data Type: String Value: F:\apps\excel\Expense_Report_Form.xls2. Run Package Nomally (w/o variable) Excel Source: I set this up to point to the Excel File and tested with the Data Conversion and OLE DB Destination. Execute the package and it runs as expected. Moves Excel data to SQL Server. 3. Create expression (Connection Manager)In the connection manager for the excel source create a expression Property: ConnectionStringExpression: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"When I click evaluate expression it returns the following: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\apps\excel\Expense_Report_Form.xls;Extended Properties="Excel 8.0;HDR=YES";As soon as I setup the expression the excel source gets a red X with error "an OLE DB error has occurred." If I try and execute the package it fails at that step. What am I missing here?? Right now I have delay validation set to "false" on the connection manager properties. I am beyond frustrated at this point. I would greatly appreciate any advice you have to offer. Thanks in advance:-)Wed, 27 May 2009 21:52:45 GMTjbsaari