SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Programming


SSIS Programming

Author
Message
U.K Padmaja
U.K Padmaja
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 11
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/Upadmaja/3003.asp
Ram-425798
Ram-425798
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 69

Ms. Padmaja!

First of all, an exemplary topic.

As you mentioned, I would like to use other approach that is to use the API to create, configure and run packages from own applications.

I am using C#. What DLLs I must add it to this?

Thanks and looking forward to hear from you.

-


AndreQ1-315878
AndreQ1-315878
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1

Given the great detail that's possible, and may be required, would it be possible to create additional libraries of functions that have many defaults set up. For example, the code below (taken from your article) would appear to be used for most text data files:

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, True)
cnFile.Properties("DataRowsToSkip").SetValue(cnFile,0)
cnFile.Properties("RowDelimiter").SetValue(cnFile,vbCrLf)
cnFile.Properties("TextQualifier").SetValue(cnFile,"""")
cnFile.Properties("HeaderRowsToSkip").SetValue(cnFile, 0)
cnFile.Properties("HeaderRowDelimiter").SetValue(cnFile,vbCrLf)
cnFile.Properties("CodePage").SetValue(cnFile,1252)

It is not yet apparent to me the benefit of this technique over DTS for the majority of file transformations.

Let me know if my reasoning on this issue makes sense.

Thanks

AndreQ1


U.K Padmaja
U.K Padmaja
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 11

Ram:

I have given the assemblies to be referred in the article itself (see 3.Getting started)

AndreQ1:

You could combine the common (or default) settings into functions and can then be called appropriately.

For simple file transfers, DTS and SSIS may not differ much. When it comes to Transformations, SSIS provides many new features when compared to DTS. You can have a look at MSDN for the different Transformations supported in SSIS. Hope that helps.


Ralph D. Wilson II
Ralph D. Wilson II
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 38

After reviewing the article, I think it is probably an excellent article . . . if I wanted to create the SSIS package in a .NET language. However, since I find writing a program in order to program an SSIS package to be, IMHO, taking the longest possible route to get from point A to point B, I believe that the title of the article is misleading. THe title should be something more like "Programming an SSIS Package in .NET".

What I expected, from the title, was an article on developing an SSIS package from within BIDS. Needless to say, I was disappointed; howver, I will add this to my Briefcase on the off chance that I may need to perform a similar task.


Ram-425798
Ram-425798
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 69
Thanks Padmaja!
tk-639222
tk-639222
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 65
Hi Padmaja, Thank you for posting the code.


But, I still have a problem while loading the header array. Below is my code

Public Class ScriptMain
Sub Main()
' Create a package and add a Data Flow task.
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()
package.PackageType = DTSPackageType.DTSDesigner90
package.Name = "MySSISPackage"
'Add Data Flow task
Dim taskDF As TaskHost = TryCast(package.Executables.Add("DTS.Pipeline.1"), TaskHost)
taskDF.Name = "DataFlow"
Dim DTP As MainPipe
DTP = TryCast(taskDF.InnerObject, MainPipe)

' Creating OLEDB Connection Manager
Dim oleDbConn As ConnectionManager = package.Connections.Add("OLEDB")
oleDbConn.Name = "MyOLEDBConnection"
oleDbConn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=" & Dts.Variables("DestServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("DestDB").Value.ToString & ";Integrated Security=SSPI;"
MsgBox(Dts.Variables("DestServer").Value.ToString)
MsgBox(Dts.Variables("DestDB").Value.ToString)

'Creating FlatFile Connection Manager
Dim cnFile As ConnectionManager = package.Connections.Add("FLATFILE")
cnFile.Properties("Name").SetValue(cnFile, "MyFlatFileConnection")
cnFile.Properties("ConnectionString").SetValue(cnFile, Dts.Variables("SourceFile").Value.ToString)
cnFile.Properties("Format").SetValue(cnFile, "Delimited")
cnFile.Properties("Unicode").SetValue(cnFile, False)
cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, True)
cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)
cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)
cnFile.Properties("TextQualifier").SetValue(cnFile, """")
cnFile.Properties("HeaderRowsToSkip").SetValue(cnFile, 0)
cnFile.Properties("HeaderRowDelimiter").SetValue(cnFile, vbCrLf)
cnFile.Properties("CodePage").SetValue(cnFile, 1252)

Dim MyFlatFilecn As SSISRuntime.IDTSConnectionManagerFlatFile90 = Nothing

MyFlatFilecn = TryCast(cnFile.InnerObject, SSISRuntime.IDTSConnectionManagerFlatFile90)
DtsConvert.ToConnectionManager90(cnFile)

Dim name As SSISRuntime.IDTSName90
Dim col As SSISRuntime.IDTSConnectionManagerFlatFileColumn90
cnFile.AcquireConnection(Nothing)

' Add columns to the FlatFileConnectionManager

Dim headers As String()
headers = Split((Dts.Variables("col1").Value.ToString), ",")
'Dim headers As String() = {"col1", "col2", "col3", "col4"}
'Get the header row from flat file. This can be stored in a database table as well.
'Assuming that "headers" Array contains the headers
Dim i As Integer = 0
While i < headers.Length
col = MyFlatFilecn.Columns.Add()
If i = headers.Length - 1 Then
col.ColumnDelimiter = vbCrLf
Else
col.ColumnDelimiter = ","
End If
col.ColumnType = "Delimited"
col.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR
name = TryCast(col, SSISRuntime.IDTSName90)
name.Name = headers(i)
'get the converSion information from the destination table
'this should assign the appropriate datatype, precision, scale and length to col
'getConversionInfo(col, name.Name) 'implement this
i = i + 1
End While
cnFile.ReleaseConnection(Nothing)

'Creating Data Flow which does the actual job of copying the data from flat file to table
'For that, creating flat file Source

Dim DFSource As IDTSComponentMetaData90
DFSource = DTP.ComponentMetaDataCollection.New()
DFSource.ComponentClassID = "DTSAdapter.FlatFileSource"
DFSource.Name = "FlatFileSource"

Dim Sourceinst As CManagedComponentWrapper
' Get the design time instance of the component.
Sourceinst = DFSource.Instantiate()
' Initialize the component.
Sourceinst.ProvideComponentProperties()
' Specify the connection manager.
'If DFSource.RuntimeConnectionCollection.Count > 0 Then
DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("MyFlatFileConnection").ID()
DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("MyFlatFileConnection"))
'End If
' Reinitialize the metadata.
Sourceinst.AcquireConnections(Nothing)
Sourceinst.ReinitializeMetaData()
Dim exOutColumn As IDTSExternalMetadataColumn90
For Each outColumn As IDTSOutputColumn90 In DFSource.OutputCollection(0).OutputColumnCollection
exOutColumn = DFSource.OutputCollection(0).ExternalMetadataColumnCollection(outColumn.Name)
Sourceinst.MapOutputColumn(DFSource.OutputCollection(0).ID, outColumn.ID, exOutColumn.ID, True)
Next
Sourceinst.ReleaseConnections()

'Add the OLEDB Destination
Dim DFdestination As IDTSComponentMetaData90
DFdestination = DTP.ComponentMetaDataCollection.[New]()
DFdestination.ComponentClassID = "DTSAdapter.OLEDBDestination"
DFdestination.Name = "OLEDBDestination"

Dim DestInst As CManagedComponentWrapper = DFdestination.Instantiate()
DestInst.ProvideComponentProperties()
If DFdestination.RuntimeConnectionCollection.Count > 0 Then
DFdestination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("MyOLEDBConnection").ID()
DFdestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("MyOLEDBConnection"))
End If

'Set the customproperties of the source
DestInst.SetComponentProperty("OpenRowset", Dts.Variables("DestTable").Value.ToString)
DestInst.SetComponentProperty("AccessMode", 0)
'reinitialize the Component
DestInst.AcquireConnections(Nothing)
DestInst.ReinitializeMetaData()
DestInst.ReleaseConnections()

'Map a Connection betwen the source and destination
Dim path As IDTSPath90 = DTP.PathCollection.[New]()
path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFdestination.InputCollection(0))



Dim input As IDTSInput90 = DFdestination.InputCollection(0)
Dim destinationInputID As Integer = CInt(input.ID)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' This will create an input column on the component.
DestInst.SetUsageType(destinationInputID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Get input column.
Dim inputColumn As IDTSInputColumn90 = input.InputColumnCollection.GetInputColumnByLineageID(vColumn.LineageID)
' Getting the corresponding external column.
' Ex : We will use the column name as the basis for matching data flow columns to external columns.

Dim externalColumn As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection(vColumn.Name)
' Tell the component how to map.
DestInst.MapInputColumn(destinationInputID, inputColumn.ID, externalColumn.ID)
Next
'Validate the package
MsgBox("Validating the package")
Dim pkgStatus As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

'System.Console.WriteLine("Validationresult: " & pkgStatus.ToString())
'Save the package
Dim SSISExe As New Application()
SSISExe.SaveToXml("C:\temp\MySSISPackage.dtsx", package, Nothing)


'Execute the package

If pkgStatus = DTSExecResult.Success Then
MsgBox("Executing the Package")
Dim pkgResult As DTSExecResult = package.Execute()
MsgBox("Package validation success")
Else
MsgBox("Package Validation failed")

End If

End Sub

End Class


As you can see in the above code, I have tried to pass the column names into the header array in 2 ways. When I give the column names directly into the array like:
(Dim headers As String() = {"col1", "col2", "col3", "col4"}) script is going good and the data is loaded into the destination table. But When I am trying to pass the column names using a variable like:
( Dim headers As String()
headers = Split((Dts.Variables("col1").Value.ToString), ","), it throws the following error

Error: The script threw an exception: Exception from HRESULT: 0xC0010009,DTS_E_ELEMENTNOTFOUND

Please let me know, where am I going wrong
JBANKO
JBANKO
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 79
I'm using a VERY simple script that works to change my flat file connection path but can't get the syntax to set the DataRowsToSkip property of the connection. To change the path I'm using:

Dts.Connections("ReturnDataFile").ConnectionString = Dts.Variables("FullPath").Value.ToString

How would I change the datarowstoskip for an integer variable HEADR:

Dts.Connections("ReturnDataFile").Properties("DataRowsToSkip") = Dts.Variables("HEADR").Value


?????????????

Thanks,

Joe B
umair.zubairy
umair.zubairy
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 28
Hi,
It is very useful article at certain extent. I have a situation. I have to create an excel file and have to load data in different excel sheets of same excel file, and also i have to create excel file at runtime. Can anybody help me out in this regard

Thanks
Umair Zubairy
James Collett
James Collett
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 35
I'm confused.

The article seems to be about using the SSIS instalation that comes with SQL Server 2005. However, the links out to the Microsoft web site reslove to pages that deal with SQL Server 2008, which is quite a different product.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search