Transform Data Task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Transform Data Task

Author

Topic

SQLfriends
Starting Member

12 Posts

Posted - 2002-10-30 : 14:41:34

I am working on a package that will query some tables and export their result sets to a Excel Spreadsheet. I used the Transform Data Task on the Design Sheet, can I make the destination(Excel file name) dynamic instead of specifying a static file name? This probably cannot be done from the Design Sheet. I might have to dig into the code. Is there a way to get the script version of the whole package after creating a DTS package using the Design Sheet?

Thanks.

Thanks.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-10-30 : 14:45:05

See if this helps:
http://www.sqldts.com/default.aspx?6,101,200,0,1

--
HTH,
Vyas
http://vyaskn.tripod.com

sherrer

64 Posts

Posted - 2002-10-30 : 16:00:31

You can use a script task with VB Script or use an outside devlopment tool to access the package throught the DTS library. The file name is simple the connection datasource for the data pump. Here is some example code written in VB.

Dim DTSFileConnect As DTS.Connection
Dim intCounter As Integer

Set DTSAddressLoad = CreateObject("DTS.Package")
DTSAddressLoad.FailOnError = True

Select Case intProcessCode
Case 0 'Creating city/state and zip4 temp tables
DTSAddressLoad.LoadFromSQLServer "ServerName", "UserNm", "Pwd", , , , , "Import Address p1"
Case 1 'CityState data loading...
DTSAddressLoad.LoadFromSQLServer "ServerName", "UserNm", "Pwd", , , , , "Import Address p2"
Set DTSFileConnect = DTSAddressLoad.Connections.Item("Grab CityState")
DTSFileConnect.DataSource = strFlatFileNameAndPath

Microsoft recommends that you use the following statement to clean up and shut down the package before unloading the object.
DTSAddressLoad.UnInitialize
Set DTSAddressLoad = Nothing

Hope this helps a bit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources