DTS Package to Output file 1

[Rhys666]

I'm fairly experienced with SQL Server and TSQL, but usually write GUI's, (.Net, VB 6.0 etc), and therefore don't have the same level of experience with DTS as I do with sProcs etc.

I need to create a DTS package, which will output a table as a CSV file to fully qualified network location. I have the string of the Network path in a table, and the filename.

I know how to use DTS to output a table to a text file on the network but don't want to essentially hard code the filename and path in the package. I can easily get the Network path and filename from a table, and am happy I'll be able to get them into variables.

How would I then give the variables to the Text File (Destination) in the SQL Server 2000 DTS Designer, and output the chosen table to the path, with the given filename?

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[mkal]

I haven't looked into this to much myself but I think if you created global variables this could solve your problem.

 

[Rhys666]

I thought this would be the way. It's passing those variables to the OutputFile (Destination) for the file name and path I'm a little blonde about.

Using the designer, the OutputFile (Destination) requires a path and filename setting up. How do I pass this the contents of the global variables?

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

How will the DTS kick-off ?

* Scheduled job
* called from another application?



From VB I'd do the following . . . (TESTED!)

'Instanciate the DTS package object
Set objPackage = New DTS.Package2

'Set the server/username/password and the DTS package to be run
objPackage.LoadFromSQLServer "SERVER IP", "LOGIN USER", "LOGIN PWD", DTSSQLStgFlag_Default, "", "", "", "DTS_NAME"

'Pass the date values to the DTS Package
objPackage.GlobalVariables("FromDate&quot.Value = dtpFrom.Value
objPackage.GlobalVariables("ToDate&quot.Value = dtpTo.Value

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'Run DTS Package
objPackage.Execute

objPackage.UnInitialize

'Mark objects for garbage collection
Set objPackage = Nothing

"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Rhys666]

Unfortunately I'm having to follow requirements and DBA's rules. The Package needs to be scheduled hourly, outputting the contents of a derived table built by a Stored Procedure. The sProc runs every 10 minutes to rebuild the table.

Also, the scheduled task should be calling the Package saved as a file on the server, so that when changes are made, the scheduler doesn't need to be changed to reflect the generated DTS name. I can see from other packages already in place how to do this, it's just that element of defining the output file and path within a package created in the SQL Server 2000 DTS designer.




Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

You can easily read the output file name and path from the registry or some other place (ini) call dll ect. through VB scripting / J script / other within the scheduled task.

If you go to task / properties / steps / edit . . .
select language . . .

do your code . . .
VB Script

Dim ParamValuesLoader

Set ParamValuesLoader = CreateObject("evsEdtParamtrs.clsEdtParamtrs&quot
Call ParamValuesLoader.RebuildAllParameterValues (False)
Set ParamValuesLoader = Nothing

"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Rhys666]

That's where I'm getting lost, (apologies if I havn't explained very well). The values for filename and path will be stored in db tables. I can get a handle on these and put them into variables. How do I output a table to that path and filename however?

Playing with the DTS Designer, you can simply drag 'n drop the SqlConnection, transform data task and TextFile (Destination) onto it. These however, then require you to select the relevant Sql Server, db, Text File, Path etc.

How can I use my variables to dynamically set the SqlServer and Database of the MS Ole DB Provider for Sql Server, and Path and FileName of the Destination Text File when the scheduled package executes?

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

If I am understanding you corerctly - you need to create a connection to the server . . . get data from the table (filename & path) for the parameters and pass the parameters ?

Then something like this . . . (haven't done this before)

dim conn
dim rs
dim sql
dim dts

set conn = CreateObject("ADODB.Connection&quot
conn.provider="sqloledb"
conn.CommandTimeout = 0
conn.ConnectionTimeout = 240

'Connect to
conn.open "Server IP", "LOGIN NAME", "LOGIN PWD"
conn.DefaultDatabase = "Northwind" (eg)

set rs = CreateObject("ADODB.Recordset&quot

sql = "select . . . "

rs.open . . . . .

dts.GlobalVariables("Path&quot.Value = rs(0)

I don't know if this is what you are looking for . . .




"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Rhys666]

Possibly.

One of my biggest problems is that of not really having used VB in anger fo over 18 months, (I've been dotNetted), so I'm struggling to get back to the VB of it, and that may be what I need here.

It does seem to make sense to do it all with an ActiveX task. As above, treat the table as a recordset, iterate thru it, writing it to a file defined by the DTS variables giving me a path and filename.

I guess I was hoping I could set up a simple drag n drop package with pre-defined Ole Db and Text File connections and a simple Transformation task to select all records from the table I'm interested in, and then change the connection properties of the Ole Db and Text File objects on the fly by assigning database records to DTS variables, and then the values in the variables to the connection objects.

But, you reckon maybe an ActiveX task then?

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

This would be the easier way - 4 me

You could do it from a stored proc aswell . . . as you said your SQL & T-SQL is quite good.

There are some examples on the net - goto

http://www.sqldts.com

* Global Variables and SQL statements in DTS
* Global Variables and Stored Procedure Parameters

Good luck!




"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Rhys666]

Cheers. It's the Vb that's killing me at the moment, I've not done any in so long, anything fairly basic is leaving me blank. Now if I could use .Net that would be simple, but that functionality's not gonna be in SQL Server for a while yet unfortunately.

Oh Well.

Thanks for the help.

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[tb]

Dankie vir die sterretjie!

Thanks 4 the star!



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Rhys666]

S'ok, no worries, all help is appreciated!



Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.