Get DTSx/SSIS Connections From C#

Categories: DatabaseDevelopment Notes; Tagged with: ; @ 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:

image

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: ; @ November 25th, 2012 17:52

Requirement:

Append additional header/footer to the destination flat file:

image

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)
Next
outStreamWriter.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: ; @ 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: ; @ 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: ; @ November 1st, 2012 16:41

需求:

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

解决方案:

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

image

不论是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



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.