SSIS 64-bit loading data from Excel Error solution 解决64位SSIS下Excel数据加载错误
Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ October 1st, 2012 17:36I’m a brand new to SSIS, when I was trying to load data from Excel, I got some Error:
Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package “Package1.dtsx” finished: Success.
“the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets,”
“By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True.”
— 64 bit Considerations for Integration Services
http://msdn.microsoft.com/en-us/library/ms141766(v=sql.105).aspx
Solution:
config Run64BitRuntime = false;
>Project > %packageName% Properties…
Ref: The Excel Connection Manager is not supported in the 64-bit version of SSIS
SSIS: Setting connectin info dynamically using dtexec.cmd in CMD
Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ September 28th, 2012 18:41SSIS is a good ETL tool, if you want to get more control/flexiblity, you can use dtexec Utility.
After finish your design work, you can invoke your package using:
dtexec /f C:\Test\Package.dtsx /Conn ConnTest2;"Server=DB_GUOLIANGS1;Uid=guoliang;Pwd=pw;" /Conn file;"C:\Testout.txt"
We can totally set the connection info dynamically.
More info:
1. Setting dynamic connection string for dtexec.exe in Powershell
http://g-m-a-c.blogspot.sg/2010/12/setting-dynamic-connection-string-for.html
2. dtexec Utility
http://msdn.microsoft.com/en-us/library/hh231187.aspx
SSIS: Config Connection info through configuration file/SQL
Categories: Database; Tagged with: DTSX • SQL Server • SSIS; @ September 27th, 2012 14:41It’s not a good idea to hardcode the connection info in the pacakge, a better solution is store connection info in SSIS package configurations.
SSIS support different configuration type: XML/Environment variable/Registry entry/ Parent package variable/SQL server.
How to config it?
SSIS > Package configurations, New… chose the paramters you want to config.
then modify it in the configuration file or database.
Test: open your pacakge using Execute package utility, If you are going to use configuration file, you need to add the config file before executing, open the ‘Configurations’ tab, add your config file. if you are using SQL Server to store your configurations, no need to do this.
then, run your pacakge.
But SSIS can not encrypt data, there are 2 solutions:
i. using configuration file: NTFS encryption and/or ACLs to control access to config files and contents, or use third party tools.
ii. using SQL server: will store config info in database, use SQL security to control access.
1. SSIS Package Config File Encryption
2. encrypting ssis package configuration file
SSIS: Export data to CSV with column names 导出数据到CSV并包含标题
Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ September 7th, 2012 15:38When exporting data to “csv” using SSIS, I found there was no column name in the output file. And the solution is quite simple and inelegant,
refer to the following screen capture:
SSIS: Can not execute dataflow in Visual Studio
Categories: Database; Tagged with: SQL • SQL Server • SSIS; @ September 6th, 2012 19:24This happened when we just edit the dtsx file.
If you want execute the flow in VS directly, create one Integration services project first, then you can copy your dtsx and pase it into the “SSIS Packages” in the Solution Explorer:
then you can execute the ssis dataflow directly in VS.