SQL Server

SQL Server Singapore User Group Event: What’s New in SSIS 2012

下班后跟同事一起去微软参加SSIS的Event.

公司使用DTS/SSIS已经很久了, 最早的一批package还是跑在SQL 2000上的, 也就是DTS. 这也是我最近工作的主要内容, 了解每一个package的细节, 然后像新版本靠近.

DTS/SSIS有个要命的地方: 如果你要了解一个package, 那就要打开这个package, 在UI上来回拖动—当mapping很多的时候, 真是要命啊! 更要命的是, 当我在research DTS时, 我没有production的访问权限! 只能蹲在DBA电脑上打开每一个DTS然后截屏….

SSIS似乎还是老样子, 如果能导出一个具有一定可读性的文档就好了.

我没有SQL的经验, 更没有DTS/SSIS的经验, 所以很早就报名参加, 可惜一直没有收到回复. 不过签到的时候看到自己名字在列表里了.

有一些marketing的东西在里面, SSIS有些细节处理的很粗糙, 2000/2005/2008 都有很粗糙甚至很粗暴的地方, 这个谁用谁知道, 但核心的功能做好了就Ok, 表面的东西其实也只是在设计的时候才会有体会.

前面有一些很细节的地方, 譬如UI上会有Getting Started啊, 撤销操作啊什么的. 有一个不错的改进:

New variable mapping — 支持复制粘贴哎! 如果你有看过DTS的mapping, 你就知道这个功能有多方便了!(其实一开始就应该有的)

catalog啊, deploy到DataBase后看起来用的很方便, logging跟report看起来也很给力!

可惜, 可惜我们还不知道几时才会迁移到新版本…

总体来讲, SSIS是个不错的工具, 很方便也很强大, 只是可能需要更细致的挖掘用户的需求, 同时还要消除一些management level的困惑. 譬如我们现在没有100%依赖SSIS的一个原因: 对于整个package, 如何部分re-run? 对于使用config file的敏感信息, 如何加密? 不过幸好SSIS在不断改进, 希望SSIS可以做的更好!

Singapore SQL Server User Group 的Facebook:
http://www.facebook.com/groups/sqlugsg/

PPT也可下载.

SSIS 64-bit loading data from Excel Error solution 解决64位SSIS下Excel数据加载错误

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ October 1st, 2012 17:36

I’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:41

SSIS 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:41

It’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:04

Use 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

Newer Posts <-> Older Posts