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
中秋2012
Categories: 垃圾山; Tagged with: 中秋节; @ September 30th, 2012 23:22中秋来了, 长假开始了.
团圆, 团圆, 又别离.
四散天涯,
所谓梦想, 不过是随波逐流.
所以啊,
漂泊着的人们,
为了家, 奔跑吧!
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
SQL Server: Export data to CVS with headers using BCP
Categories: Database; Tagged with: BCP • CSV • ETL • SQL • SQL Server • T-SQL; @ September 25th, 2012 14:04Use this BCP script to generate CSV file:
bcp "SELECT ''ID'', ''Username'' UNION ALL SELECT CAST(u.ID as varchar(10)),u.USERNAME from DavidTest.dbo.DavidUser u" queryout "c:\output.csv" -c -t"," -r"\n" -SServerName -T
If you want to run in SSMS, you can use:
USE MASTER
GO
DECLARE @query varchar(4000)
SET @query = '"SELECT...."';
EXEC xp_cmdshell @bcpOut
GO
for more info:
http://msdn.microsoft.com/en-us/library/aa174646(v=sql.80).aspx