| Author |
Topic |
|
negotiator
Starting Member
4 Posts |
 Posted - 2003-11-04 : 13:02:15
|
| Hi,I'm currently working on a solution, to build import- and export-DTS-packages dynamically in VB and run them from VB (SQL-Server 7 and VB6). Import works fine, but export seems to be too tricky for me.First of all, I create a DTS-Package: Set moPackage = New DTS.Package With moPackage .Name = sPackageName .Description = "Import files to Database" ... End With2nd: Adding connections Dim oConnection As DTS.Connection Set oConnection = moPackage.Connections.New("DTSFlatFile.1") With oConnection .Name = "TXTFile" .ID = 2 'The unique connection ID .Reusable = True 'The default is FALSE. .ConnectImmediate = False 'The default is FALSE. .DataSource = "c:\tmp.txt" .ConnectionProperties("Row Delimiter").Value = Chr(13) & Chr(10) .ConnectionProperties("Column Delimiter").Value = ";" .ConnectionProperties("Mode").Value = 3 .ConnectionProperties("File Format").Value = 1 .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Skip Rows").Value = 0 .ConnectionProperties("First Row Column Name").Value = False .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Text Qualifier").Value = Chr(0) End With moPackage.Connections.Add oConnection Set oConnection = Nothing[quite similar for SQLOLEDB.1-connection to DB, uses ID = 1]Then I add a task: Dim oTask As DTS.Task Dim oCustomTask As DTS.DataPumpTask Dim oTransformation As DTS.Transformation Set oTask = moPackage.Tasks.New("DTSDataPumpTask") Set oCustomTask = oTask.CustomTask With oCustomTask .Name = "ExportFileTask" .Description = "DB-table to FlatFile transformation" .SourceConnectionID = 2 .SourceSQLStatement = "SELECT column1, column2 FROM TestTABLE" .DestinationConnectionID = 1 .FirstRow = 1 .LastRow = 0 .AllowIdentityInserts = False End With Set oTransformation = oCustomTask.Transformations.New("DTS.DataPumpTransformCopy.1") With oTransformation .Name = "ExportFileTransformation" .TransformFlags = DTSPump.DTSTransformFlag_Default .ForceSourceBlobsBuffered = DTSForceMode_Default .ForceBlobsInMemory = False .InMemoryBlobSize = 1048576 End With oTransformation.SourceColumns.AddColumn "column1", 1 oTransformation.SourceColumns.AddColumn "column2", 1 oTransformation.DestinationColumns.AddColumn "column1", 1 oTransformation.DestinationColumns.AddColumn "column2", 1 oCustomTask.Transformations.Add oTransformation Set oTransformation = Nothing moPackage.Tasks.Add oTask Set oCustomTask = Nothing Set oTask = NothingAdd Step: Set oStep = moPackage.Steps.New With oStep .Name = "ExportFileStep" .Description = "dummy" .TaskName = "ExportFileTask" .AddGlobalVariables = True .CloseConnection = False .IsPackageDSORowset = False .DisableStep = False .JoinTransactionIfPresent = False .CommitSuccess = False .RollbackFailure = False .ExecuteInMainThread = False .RelativePriority = DTSStepRelativePriority_Normal End With moPackage.Steps.Add oStep Set oStep = NothingTo reuse this package, I assign the database- and filename at the end: Set oConn = moPackage.Connections("TXTFile") oConn.DataSource = "K:\[...]\export_file.txt" Set oConn = Nothing ' Set Database Set oConn = moPackage.Connections("DBConnection") oConn.Catalog = "TestDB" Set oConn = NothingFinally I run the package with: moPackage.ExecuteBut I only get the errormessage: "Incomplete file format information - file cannot be opened."Any suggestions what's wrong with this code?Thanks in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-04 : 13:08:44
|
| It seems as though your format file is incorrect. Instead of figuring this out the hard way, let's start simple. Using your format file, can you run bcp with it for an export? Get the format file to work first, then put it in your code.Tara |
 |
|
|
negotiator
Starting Member
4 Posts |
Posted - 2003-11-04 : 14:57:22
|
| I'm quite sure, that I don't need a format-file if I use DTS. If I would use BCP, then I'd need to create such a file, but this is too "static" for my requirements.The export-package should be able to build it's export-format as a result from some tables, therefore I chose DTS instead of BCP.Thanks,Oliver |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-04 : 15:17:14
|
| The point that I am making is that you are making things too complicated. You have to start simple. Use the DTS designer first to get a simple package that works. Then save the package as a VB file. Then modify the VB file for your needs.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-04 : 18:39:03
|
quote:
If I would use BCP, then I'd need to create such a file, but this is too "static" for my requirements. The export-package should be able to build it's export-format as a result from some tables, therefore I chose DTS instead of BCP.
Can't be any more dynamic than what Brett has here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=2919110 minutes of tweaking, and you'll be able to make a stored procedure out of that that will accept a table name and generate a bcp format file for you. You could even call that procedure using bcp to generate the format file. It's also driven completely off the format of the table, and won't require any code changes if the table's structure changes. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-05 : 01:14:14
|
| >> The export-package should be able to build it's export-format as a result from some tables, therefore I chose DTS instead of BCP.????What makes you say that.Why would you need a format file for a bcp export? Just use a query.One thing that bcp is useful for compared to dts is that it is easy to get dynamic structures, table names, filesnames and locations.seehttp://www.nigelrivett.net/ImportTextFiles.htmlhttp://www.nigelrivett.net/MoveImportData.htmlI didn't bother posting the export side because it is simpler but you can easily get the output tables and query from data and aslo the filenames and path and build the bcp.You could also alter the source and destinations in a dts package.Here's the one for the server, database and filenamehttp://www.nigelrivett.net/SetDTSRunTimeValues.htmlEasiest just to alter the source query to get the output structure.If you're importing/exporting to/from sql server and have the definitions in sql server why not do it all from SPs rather than VB then you could just call the SP from VB?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-05 : 06:43:14
|
| Not sure about Brett's s-proc. BCP utility has "format"option that for creating format file only (w/o any bulk copying). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-05 : 07:33:15
|
| Oooooooooooops, forgot about that, thanks Stoad! |
|
|
|
negotiator
Starting Member
4 Posts |
Posted - 2003-11-05 : 07:56:51
|
| First of all thank you all for your support.Due to the greater flexibilty of DTS, I'd prefer to run DTS instead of BCP (ActiveX-transformations, conversion of date-formats, error handling, syntax and semantic-cheks). I already use DTS for import (quite the similar way as described in my first posting) and things work very well.Given the fact, that the import already works, from my point of view it shouldn't be too difficult to adatpt this for an export. Is there anybody who has done this in the past?If I save the package to the SQL-Server repository, everything looks fine execpt for the "Destination"-tab in the transformation properties. If I click on this, a window pops up immediately and I'm asked to define columns. When these columns have been defined manually, the package works fine.Any suggestions?Many thanks in advance,Oliver |
|
|
|
|
DTS-Export using VB