ETL

Get DTSx/SSIS Connections From C#

Categories: Database • Development Notes; Tagged with: C# • DTS • DTSX • ETL • SSIS; @ December 5th, 2012 23:49

Requirement: Get all connections from dtsx packages using C#.

Solution:

Using Microsoft.SqlServer.Dts.Runtime to load the package, and then get all connections.

Step1 Add reference

In case you are totally new to .Net/C#, you may need this screen capture:

In the pop-up window, click “Browse” tab, and select: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Microsoft.SQLServer.ManagedDTS.dll

About the dll path:

ref 1: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c333d8e5-a4ca-470e-a2d8-4f67a5e05a10/

ref 2: http://stackoverflow.com/questions/4920591/havent-got-microsoft-sqlserver-manageddts-dll-but

Step 2 Coding

import: using Microsoft.SqlServer.Dts.Runtime

        public void testDts()
        {
            string pkgLocation;
            Package pkg;
            Application app;
            DTSExecResult pkgResults;

            pkgLocation =
              @"C:\Users\x\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\" +
              @"Package.dtsx";
            app = new Application();
            pkg = app.LoadPackage(pkgLocation, null);

            Connections conns = pkg.Connections;
            foreach (ConnectionManager cm in conns)
            {
                Console.WriteLine("Name = " + cm.Name + ", HostType = " + cm.HostType + "; ConnectionString=" + cm.ConnectionString);
            }
        }

API: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.connections(v=sql.100).aspx

Output:

Name = Flat File Connection Manager, HostType = ConnectionManager; ConnectionString=C:\Users\x\Desktop\ssisTest.txt
Name = x_instan, HostType = ConnectionManager; ConnectionString=Data Source=x-PC\x_INSTAN;Integrated Security=SSPI;Connect Timeout=30;

SSIS: Append Header/Footer to flat file

Categories: Database; Tagged with: CMD • DTSX • ETL • SSIS • VB; @ November 25th, 2012 17:52

Requirement:

Append additional header/footer to the destination flat file:

Solution:

Header/Footer can be added by several ways in SSIS, here is 3 ways I’m using:

Usinig Union in SQL :


-- Header  
Select 'START' + convert(varchar(8),GETDATE(), 112)  
-- Body  
Union All  
Select FULLNAME  
from OVERSEA_ADDRESS  
WHERE POSTAL_CODE = ''  
-- Footer  
Union All  
Select 'END'+ Convert(varchar(8), Count(*))  
from TWO_FA_OVERSEA_ADDRESS  
WHERE POSTAL_CODE = ''

But sometimes the requirement is more complex, then we need to use Script task.

Script task using VB/C#

We can read/write file using VB or C#, and we can get the variables/paremeters, so basically, we can handle most of the requirement by Script task.

Reading file using VB:

Dim objReader As New System.IO.StreamReader(FILE_NAME)

' Read each line, and append the text into textLines Dim currentLine As String Do While objReader.Peek() <> –1 currentLine = objReader.ReadLine() textLines.Add(currentLine) Loop

Writing to file

' For loop the textLines Dim index As Integer For index = 0 To textLines.Count – 1 Dim text As String = textLines.Item(index).ToString outStreamWriter.Write(text & vbNewLine) NextoutStreamWriter.Close()

Using copy CMD

Generate the header, body, foot file and use CMD to combine the files:


copy header.txt+body.txt+footer.txt output.txt /y

SSIS: Get Variables/Properties In Script task

Categories: Database; Tagged with: DTSX • ETL • SQL Server • SSIS • VB; @ November 25th, 2012 15:52

Requirement:
Get variables/properties in Script task.

Note: Do remember to set the variables in the component editor.

Solution(in VB):

1. Get properties:
MsgBox(Dts.Connections(“fileDest”).ConnectionString)

2. Get variables:
MsgBox(Dts.Variables(“User::Counter”).Value.ToString)

Change Script Task Script Language in SSIS 2005

Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ November 2nd, 2012 18:02

The answer in we can no choose language in 2005, “The scripting language is Microsoft Visual Basic .NET.” no other options. http://msdn.microsoft.com/en-us/library/ms187649(v=sql.90).aspx

SSIS文件操作:复制/删除/移动/重命名

Categories: Database; Tagged with: ETL • SSIS; @ November 1st, 2012 16:41

需求：

通过SSIS复制/删除/移动/重命名文件

解决方案：

使用File System Task组件进行操作。复制文件的例子：

不论是Source Connection 还是Destination Connection, 都可以Connection 或是 Vaiable.

上例中Source使用的是一个指向要复制文件的Connection, Destination则使用Variable, 该Variable类型为String，值为目标路径，如：

\\liguoliang.com\Share\___SQ\SSISCopyTest.txt。

通过配置参数OverwriteDestination可控制是否允许覆盖文件。

常见错误：

在使用Variable时value填写错误，无法找到文件路径造成运行失败。

Newer Posts <-> Older Posts