Re: DTS object model
From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 05/05/04
- Next message: Allan Mitchell: "Re: BCP a sectional bulk of data from Table1 (in db1) to Table2 (in db2)"
- Previous message: Steven Wong: "Re: BCP a sectional bulk of data from Table1 (in db1) to Table2 (in db2)"
- In reply to: RK: "Re: DTS object model"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 5 May 2004 16:22:38 +0100
BULK INSERT has no concept of mapping columns other than through a format
file. Validation you can do through a piece of VBScript in an Active Script
task yes.
a Transformation Object applies to different tasks
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\dts
prog.chm::/dtspcoll_7g6m.htm
-- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "RK" <anonymous@discussions.microsoft.com> wrote in message news:21F443C0-10A3-470D-9D14-9BD38EC2D0AD@microsoft.com... > Allan, Thanks for your valuable suggestion. I tried with bulkinsert command in TSQL but I also need to validate the data before inserting it into sql tables. So one of my collegues suggested to go with DTS and do the validation and also mapping of columns through DTSTransformation. > > Can I do validation & mapping, if I use BulkInsert Command in TSQL? > > I appreciate all your prompt replies, > RK > > ----- Allan Mitchell wrote: ----- > > If that is all you want to do then why bother with DTS? You can use the > BULK INSERT command in TSQL and a format file. > > Your code suggests you are building a package from scratch but then you go > and ask to Load a package from SQL Server with the same name. Maybe you > wanted SaveToSQLServer ? > > -- > > ---------------------------- > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > > "RK" <anonymous@discussions.microsoft.com> wrote in message > news:59D5FF4E-D38E-4637-8EF0-B7F9F53DB880@microsoft.com... > > Allan, > >> My requirement is: > > I have a text file with Delimiter as comma and TextQualifier as > SingleQuote. I want to create a text file connection and set the > connectionproperties of delimiter and textqualifier and then by using > bulkinserttask, I want to insert data into sql server 2000 through DTS > object model in vb.net. > >> Here is my code snippet: > >> ******************** > > Code prior to modifications: > > Dim loPackage As New DTS.Package2 > > Dim loConn As DTS.Connection2 > > Dim loStep As DTS.Step > > Dim loTask As DTS.Task > > Dim loCustomTask As DTS.BulkInsertTask > >> Try > > loConn = loPackage.Connections.New("SQLOLEDB") > > ''loConn = loPackage.Connections.New("DTSFlatFile") > >> loStep = loPackage.Steps.New > > loTask = loPackage.Tasks.New("DTSBulkInsertTask") > > loCustomTask = loTask.CustomTask > >> With loConn > > .Catalog = scDatabaseName.Trim > > .DataSource = scServerName.Trim > > .ID = 1 > > .UseTrustedConnection = True > > .UserID = "" > > .Password = "" > > End With > > loPackage.Connections.Add(loConn) > > loConn = Nothing > > With loStep > > .Name = "PkgStep" > > .ExecuteInMainThread = True > > End With > > With loCustomTask > > .Name = "Task" > > .DataFile = "c:\customer.txt" > > .ConnectionID = 1 > > .DestinationTableName = scDatabaseName.Trim & > "..customertest" > > .FieldTerminator = "," > > .RowTerminator = vbCrLf > > .KeepNulls = True > > End With > > loStep.TaskName = loCustomTask.Name > > With loPackage > > .Steps.Add(loStep) > > .Tasks.Add(loTask) > > .FailOnError = True > > End With > > loPackage.Execute() > >> Catch ex As Exception > > MessageBox.Show("Error: " & CStr(Err.Number) & vbCrLf & > Err.Description, vbExclamation, loPackage.Name) > >> Finally > > loConn = Nothing > > loCustomTask = Nothing > > loTask = Nothing > > loStep = Nothing > > If Not (loPackage Is Nothing) Then > > loPackage.UnInitialize() > > End If > > End Try > >>>> Modified to: > > Dim p As New DTS.Package > > Dim cn As DTS.Connection > >> p.Name = "MyPackage" > > cn = p.Connections.New("DTSFlatFile") > > With cn > > .Catalog = scDatabaseName.Trim > > .DataSource = "(local)" > > .ID = 1 > > .UseTrustedConnection = True > > .UserID = "" > > .Password = "" > > End With > > p.Connections.Add(cn) > > p.LoadFromSQLServer(".", , , > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , > "MyPackage") > >> For Each cn In p.Connections > > If cn.ProviderID = "DTSFlatFile" Then 'text File > > 'ideally you would know the name of the connection > > 'What is the text qualifier property > > MessageBox.Show(cn.ConnectionProperties.Item("Text > Qualifier").Value) > >> 'Now change it > > cn.ConnectionProperties.Item("Text Qualifier").Value = > "£" > >> 'Have a look > > MessageBox.Show(cn.ConnectionProperties.Item("Text > Qualifier").Value) > > End If > > Next > >> p.UnInitialize() > >> ******************** > > Error occured at p.LoadfromSQLServer() > >> Where am I going wrong? > >> I appreciate your help, > > RK > >> ----- Allan Mitchell wrote: ----- > >> What is your exact string you are using. Is it exactly the same as > mine? > >> -- > > -- > >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > www.SQLDTS.com - The site for all your DTS needs. > > I support PASS - the definitive, global community > > for SQL Server professionals - http://www.sqlpass.org > >>> "RK" <anonymous@discussions.microsoft.com> wrote in message > > news:841503A9-09D8-47C1-B2DB-E7FFA3A6CE3E@microsoft.com... > >> I am getting an error "The specified DTS Package ('Name = > 'MyPackage'; > > ID.VersionID = {[not specified]}.{[not specified]}') does not > exist." > >>> Do I need to specify any package prior to this? > >>> Thanks for all your help, > >> RK > >>> ----- Allan Mitchell wrote: ----- > >>> You want to create one or manipulate one. > >>> Whilst it is possible to build one completely from scratch I > would > > probably > >> go with manipulating it. > >>> So Say I have a Package called MyPackage and in there I have > a > >> TextFileConnection. i can do this to grab a ref to the > connection > >>>> Dim p As New DTS.Package > >>> Dim cn As DTS.Connection > >>> p.LoadFromSQLServer(".", , , > >> > DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , > > , > >> "MyPackage") > >>> For Each cn In p.Connections > >>> If cn.ProviderID = "DTSFlatFile" Then 'text File > >>> 'ideally you would know the name of the connection > >>>>> End If > >>> Next > >>> p.UnInitialize() > >>>> -- > >> -- > >>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > >> www.SQLDTS.com - The site for all your DTS needs. > >> I support PASS - the definitive, global community > >> for SQL Server professionals - http://www.sqlpass.org > >>>> "RK" <anonymous@discussions.microsoft.com> wrote in message > >> news:123ED7F8-9268-4575-82EB-2303ADAF3629@microsoft.com... > >>> Hi, > >>>> Can anyone tell me how to do: Text File connection to DTS in DTS > > object > >> model in vb.net? > >>>> Thanks, > >>> RK > >>>>
- Next message: Allan Mitchell: "Re: BCP a sectional bulk of data from Table1 (in db1) to Table2 (in db2)"
- Previous message: Steven Wong: "Re: BCP a sectional bulk of data from Table1 (in db1) to Table2 (in db2)"
- In reply to: RK: "Re: DTS object model"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
