Importing excel to SQL Server 2005 using SSIS
Instead of SQL SErver Destination , use OLE DB Destination and see if it works for you. ... But try first option first, Change your Destination to OLE DB Destination and see what you get Since the actual error is about the SQL Server Destination and not about the Excel Source, I also think this is the way to go...MCTS, MCITP - Please mark posts as answered where appropriate.
July 12th, 2011 8:52am

Thanks all, I will try all your suggestions, and get back to you Thanks & Regards Prasad DVR
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 9:38am

Koen spotted it. The problem is the SQL Server Destination, and there's a restriction there that you may not be aware of. The SQL Destination will ONLY work when the package is executed on the server, not from your machine using BIDS. Use the OLE DB Destination - the performance difference is negligible. Talk to me now on
July 12th, 2011 11:59am

Thanks Aamir, I have changed Destination from SQL Server to OLE DB and it worked for me.Thanks & Regards Prasad DVR
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 2:20am

Hi All, I am import data from excel file to Server 2005, I have created SSIS package to do this, I have used only 3 componets 1. Excel Source (The excel file is stored on my local machine) 2. Data Convertion (used this to change the data type convertion) 3. SQL Server destination (I have configured this for my dev sql server) I am getting below error message Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0202009 at Data Flow Task, SQL Server Destination [101]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.". Please help, thanks in advance Prasad Thanks & Regards Prasad DVR
July 13th, 2011 5:09am

Seems like SSIS is not able to find the source Excel file. Are you providing UNC path in Connection Manager? Or file is at local server ? Does your account have access to the location you used in Excel Connection Manager? http://sqlage.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 5:28am

Hi Aamir, Thanks for your reply. what is UNC path? yes the file is at local server. yes I have the complete access to the location, Here I got small question, for the Excel Connection Manager the Password property is showing some *s and I am unable to delete those *s, actually what are those *s represent? Thanks & Regards Prasad DVR
July 13th, 2011 5:52am

IF your file is on local server where you are running then you don't need to worry about UNC path. "A path, the general form of a filename or of a directory name, specifies a unique location in a file system. A path points to a file system location by following the directory tree hierarchy expressed in a string of characters in which path components, separated by a delimiting character, represent each directory. The delimiting character is most commonly the slash ("/"), the backslash character ("\"), or colon (":"), though some operating systems may use a different delimiter. Paths are used extensively in computer science to represent the directory/file relationships common in modern operating systems, and are essential in the construction of Uniform Resource Locators (URLs). Systems can use either absolute or relative paths. A full path or absolute path is a path that points to the same location on one file system regardless of the working directory or combined paths. It is usually written in reference to a root directory." from Wiki. link: http://en.wikipedia.org/wiki/Path_(computing) Those ** are by default . You don't have to specify password if Excel does not required Password to open. Double click on the Connection manager and then browse to the file and click on mapping and see if you are able to access the file. Thankshttp://sqlage.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 6:00am

Hi All, I am import data from excel file to Server 2005, I have created SSIS package to do this, I have used only 3 componets 1. Excel Source (The excel file is stored on my local machine) 2. Data Convertion (used this to change the data type convertion) 3. SQL Server destination (I have configured this for my dev sql server) I am getting below error message Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0202009 at Data Flow Task, SQL Server Destination [101]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.". Please help, thanks in advance Prasad Thanks & Regards Prasad DVR Hi, You're executing this package from SQL Agent? Global\DTSSQLImport is the package name? If both answer are true, probably the execution account (SQL Agent service or proxy account) should have permissions to excel file path.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
July 13th, 2011 6:22am

Victor, I am running the package from Visual BI Studio not from SQL Agent 'Global\DTSSQLImport' is not package name it is showing like that doesn't have access to the file/path. Aamir, I have done exactly what you have told, and the path is absolute path not a relative path. Thanks & Regards Prasad DVR
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 6:31am

Instead of SQL SErver Destination , use OLE DB Destination and see if it works for you. Second option to check : Create new package, make connection to Excel and then Drag Multicast and connect and run the package and see if it get the data and move downwards.. just to make sure there is no problem with Source file. But try first option first, Change your Destination to OLE DB Destination and see what you get! Thankshttp://sqlage.blogspot.com/
July 13th, 2011 7:12am

'Global\DTSSQLImport' is a path? don't seems it... there're expressions configured for source file? Regarding Aamir is saying, SQL Destination only works on local servers, i mean if the package is executing in the same destination server Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 7:52am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics