August 5, 2018 at 1:04 pm
Hi, I like to create import (Excel to table) and export (Table to Excel) job using sql import and export tool (32 bit) and create dtsx out of it. So that any one in the company wants to use it to import or export respective file/table can reuse the job instead of each one creating one. with that said, i created the job but when i give the job to another developer to use it, he faces the issue...I tried all the option of saving sensitive data with password, not to save sensitive data...Please guide me with step by step instructions based on my above req..how to create import and export job in dtsx.
Thanks
Chandra
August 6, 2018 at 2:15 pm
rchandra1 - Sunday, August 5, 2018 1:04 PMHi, I like to create import (Excel to table) and export (Table to Excel) job using sql import and export tool (32 bit) and create dtsx out of it. So that any one in the company wants to use it to import or export respective file/table can reuse the job instead of each one creating one. with that said, i created the job but when i give the job to another developer to use it, he faces the issue...I tried all the option of saving sensitive data with password, not to save sensitive data...Please guide me with step by step instructions based on my above req..how to create import and export job in dtsx.
Thanks
Chandra
It really depends on the error. I can't tell if it's permissions with the job, the package, something the package is accessing, etc. And I'm only assuming permissions due to the comments about options with the passwords. It could be something totally different.
Could you please post the error messages and any related details to that error?
Sue
August 7, 2018 at 6:19 am
Sue_H - Monday, August 6, 2018 2:15 PMrchandra1 - Sunday, August 5, 2018 1:04 PMHi, I like to create import (Excel to table) and export (Table to Excel) job using sql import and export tool (32 bit) and create dtsx out of it. So that any one in the company wants to use it to import or export respective file/table can reuse the job instead of each one creating one. with that said, i created the job but when i give the job to another developer to use it, he faces the issue...I tried all the option of saving sensitive data with password, not to save sensitive data...Please guide me with step by step instructions based on my above req..how to create import and export job in dtsx.
Thanks
ChandraIt really depends on the error. I can't tell if it's permissions with the job, the package, something the package is accessing, etc. And I'm only assuming permissions due to the comments about options with the passwords. It could be something totally different.
Could you please post the error messages and any related details to that error?Sue
SSIS and Excel do not play nicely together. I have always had issues with the 32bit/64bit driver conversions. BIDS (not user about SSDT) was a 32bit application and had to use the 32bit drivers even if your development environment is 64 bit architecture, but when the package is deployed it runs on 64 bit server and needs the 64 bit drivers. If the Excel workbook is protected in any way then the scripts will fail. If the column or sheet names change it will fail. If the data cannot be converted to the correct type it will fail (e.g. someone puts N/A in the valid to date field)
If possible, got back to the original source that generated the Excel file, or see if you can get the data in CSV format instead. you will save yourself a world of pain.
August 8, 2018 at 12:59 pm
aaron.reese - Tuesday, August 7, 2018 6:19 AMSSIS and Excel do not play nicely together. I have always had issues with the 32bit/64bit driver conversions. BIDS (not user about SSDT) was a 32bit application and had to use the 32bit drivers even if your development environment is 64 bit architecture, but when the package is deployed it runs on 64 bit server and needs the 64 bit drivers. If the Excel workbook is protected in any way then the scripts will fail. If the column or sheet names change it will fail. If the data cannot be converted to the correct type it will fail (e.g. someone puts N/A in the valid to date field)
If possible, got back to the original source that generated the Excel file, or see if you can get the data in CSV format instead. you will save yourself a world of pain.
+1 a million times over. Even if you can work round the driver issue (usually by setting the package to run in 32-bit mode) and other quirks, I've found it to be very flaky. Last time I had to do it, I had so many issues with the Jet Database driver occasionally hanging under load that I eventually gave up and just wrote a Powershell script to pre-process the Excel files and dump out the data into files that could subsequently be imported directly. It wasn't pretty but killing a relaunching a script if it hung was less hassle that restarting the integration services service.
August 8, 2018 at 2:38 pm
aaron.reese - Tuesday, August 7, 2018 6:19 AMSue_H - Monday, August 6, 2018 2:15 PMrchandra1 - Sunday, August 5, 2018 1:04 PMHi, I like to create import (Excel to table) and export (Table to Excel) job using sql import and export tool (32 bit) and create dtsx out of it. So that any one in the company wants to use it to import or export respective file/table can reuse the job instead of each one creating one. with that said, i created the job but when i give the job to another developer to use it, he faces the issue...I tried all the option of saving sensitive data with password, not to save sensitive data...Please guide me with step by step instructions based on my above req..how to create import and export job in dtsx.
Thanks
ChandraIt really depends on the error. I can't tell if it's permissions with the job, the package, something the package is accessing, etc. And I'm only assuming permissions due to the comments about options with the passwords. It could be something totally different.
Could you please post the error messages and any related details to that error?Sue
SSIS and Excel do not play nicely together. I have always had issues with the 32bit/64bit driver conversions. BIDS (not user about SSDT) was a 32bit application and had to use the 32bit drivers even if your development environment is 64 bit architecture, but when the package is deployed it runs on 64 bit server and needs the 64 bit drivers. If the Excel workbook is protected in any way then the scripts will fail. If the column or sheet names change it will fail. If the data cannot be converted to the correct type it will fail (e.g. someone puts N/A in the valid to date field)
If possible, got back to the original source that generated the Excel file, or see if you can get the data in CSV format instead. you will save yourself a world of pain.
Lordy no. Don't have users start exporting CSV or even TSV. And that won't help, either. Column names, sheet names, position of headers, addition of columns, etc, etc, ad infinitum will still be a problem with the exported CSV data.
Please see the following ZIP file for a PPT on the subject and the accompanying code and example spreadsheets.
http://glass.pass.org/Portals/453/Avoid%20Excel%20Hell%20with%20T-SQL.zip
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply