Exporting data and dynamically changing the destination file name 3

[kscheeler]

I have a DTS script that runs a stored procedure into a new table and then exports that data to a specific file. I now need to change that file name by incrementing the suffex. ie: myfile_0001.txt, myfile_0002.txt etc..

I can create a table to store the incremented number but I don't know how to dynamically change the destination file name.

Any suggestions?

Kevin Scheeler

 

[JohnDTampaBay]

Prior to the actual export task, use a Dynamic Properties task to modify the Connections properties of the text file connection. Set the DataSource to be derived from a query. Query your table for the appropriate destination file name. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[kscheeler]

John,

It worked like a charm! I think this will come in useful in other cases.

Thanks,

Kevin Scheeler

 

[TwoOdd]

Can either of you explain how to incorporate the dynamic properties task into a dts package? I'm new to dts packages and have only created them using the wizard.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[JohnDTampaBay]

Open the package to which you want to add the Dynamic Properties task. Add a Dynamic Properties task to the package. From the task dialog box, click the ADD button and a Package Properties dialog box will open. Drill down to the property that you want to dynamically set. Select the property and click the SET button.

You can set the property from a variety of sources. If you choose Global Variable as the source, then you can select the specific Global Variable from the drop-down list that holds the value for the property. If you choose QUERY, then you can select the connection and write the query to generate the value for the property. Use the REFRESH button to preview the property assignment.

I hope this helps. Good luck!



--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[TwoOdd]

I have done that, but it doesn't seem to apply the query results to the exported file. It keeps using the default file name instead of the results from the query.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TwoOdd]

I have the source set to Query.
Connection is Connection 1 (my database connection)
Query is:
SELECT [FileName] FROM tbl_FileName WHERE (Task = 'Const')

When I click on PARSE, it says the sql statement is valid. When I click on REFRESH, I get nothing.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[JohnDTampaBay]

Is the Dynamic Properties task run before the export task?

Are you updating the DataSource properties of the Connection?

Is your query returning a valid value for which the property can be set?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnDTampaBay]

OK, then you have a problem with the query. The property will not be updated until the query returns a valid value for which the property can be set.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[TwoOdd]

Yes, the Dynamic Properties task is run before the export task.

I am updating the Data Source of Connection 2 (the file being exported).

I am not sure if the query is returning a valid value. I would say yes only because when I use that particular value as the default value in the property, it works.

Outside of the Dynamic Properties Task, I only have 3 objects:

Create the table
Connection 1 (DB Connection)
Connection 2 (Exported Excel Spreadsheet)

Is there something in the workflow properties that needs to be changed?


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TwoOdd]

Got it. I thought I needed to put in the location as well as the name of the file. Apparently, it already knew where to save the file, so when I gave it just the file name, it worked.

Thank you very much for your help,


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner