Specify\Pass parameters for a destination filename ?

[rzirpolo]

I am setting up a task where a .csv file is to be converted into a .txt file. This txt file is used by another program and is dependent on the file name.

Is there anyway where the text in one of the cells of the .csv can be passed and output as part of the filename for the .txt file i.e. a parameter ?

My thinking is that if I set the DTS as a task to run this file convesrion on a regular basis the filename will not always be the same.

Any suggestions would be grateful, thanks

 

[JohnDTampaBay]

You should be able to use a Dynamic Properties Task before the data transformation to query a cell of the CSV file and dynamically set the destination filename for the Transformation Task. I haven't tested this, but it seems viable. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

'Dynamic Properties Task' ? what is that and where and how do I set this. Some more information would be useful as I would like to test it out.

Thanks

 

[JohnDTampaBay]

Create a simple DTS package that will convert the CSV to TXT. Don't worry about the destination filename for now. Rather than repeat myself, check out this thread Thread961-683072 and see if you can adapt it to your needs. If you get stuck, we'll gladly lend a hand.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

Ok, this has opened my eyes a bit now and may do what I need. I can see the parameter for the destination file but how do I get it to reference the a part of the text in the .csv file ?

My goal is to setup an automatic procedure which will read the .csv file, export to a .txt with a filename which consists of a standard set of letters along with some of the text from within the .csv

Does this make sense ?

 

[JohnDTampaBay]

It makes sense. I assume your DTS package has separate connections for the CSV file and the TXT file. In the Dynamic Properties Task, you should be able to set the value of the TXT destination filename property by querying the CSV connection, similar to how the SQL table is queried in the thread I mentioned. You may have to play with the query a bit to extract exactly what you want from the CSV. Use the REFRESH button on the Add/Edit Assignment dialog box to preview the query result.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

Ok, I really appreciate your efforts on this but looking at Thread961-683072 has me even more confused as this is in reference to table to table data. I agree the same principles apply but cannot understand if the functionality will be the same.

Looking at the menu option Package > Disconnected Edit suggest that what I am trying to achieve may be possible. What I need to configure via using query (if possible) are the parameters for DestinationObjectName and SourceObjectName.

Let me explain in more detail what I have and what I hope to achieve, I have a .csv file named 'extf200030930.csv' with many rows of data similar to this,

"RIBSA031022092714", "20030930", "#114001", "#114","","TRN","006800023"

When using DTS and converting to a .txt file I require the file name to be TRNFXFRMRIBSA031022092714.txt. Notice after TRNFXFRM the text 'RIBSA031022092714' taken from the row of data in the .csv file. My dilemma is how can I get this from a query ? I am totally clueless ?

If you think that DTS doesn't stretch this far and you know of any alternatives that would help me let me know.

 

[JohnDTampaBay]

The Dynamic Properties Task allows you to do the same things you can do with Disconnected Edit, just programatically. The cool thing about DTS is that it blurs the lines between data formats. Once you define the datasources, almost everything becomes table to table transformations. If you have created the basic DTS export package, then you have a connection to the CSV and TXT files as datasources just like a connection to SQL, Access, or any other database.

Since you've set up the CSV and TXT as datasources, DTS knows how to access the data in the files. The CSV filename seems to be date based. The thread I mentioned applies to this. Then you can query the CSV datasource to capture the data that you need to set the TXT filename. Similar to the thread I mentioned, your query may like something like this:

SELECT DISTINCT 'TRNFXFRM' + Field1 + '.txt'
FROM [extf200030930.csv]

How you we doing so far? Does this make more sense?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

Ok that makes perfect sense (much appreciated :-D). What I now need to understand is how do I actually set this up in my DTS package. Are you saying that I can insert queries like your example in the 'Edit Property' window in Disconnected Edit ? or do I set this up in a Dynamic Properties Task ?

Also... by using your example where do I specify file locations ?

I don't want to run before I can walk but looking further ahead are there any standard properties\parameters that can be called via a query ? i.e. the created date of a file for example ? If so this could be even better than I thought... I am thinking along the lines of picking up source files for specfic dates.

 

[JohnDTampaBay]

Queries work with the Dynamic Properties Task. Anything you can set manually with Disconnected Edit, you can set programatically with Dynamic Properties Task.

The file location (path) is set as part of the file name of the connection.

If you want to look at attributes of the disk file (as opposed to data in the file) I think you'll need to use an ActiveX script. ActiveX is not my forte. I think

http://www.sqdts.com

has some information on setting dynamic properties via ActiveX.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

Ok, ActiveX I can leave till later. I really do appreciate all this info but like I said I have never used DTS before so what I would ideally like is guidelines in terms of what menus I have to go into and what settings I have to set and where I enter the query, etc.

I understand if you are busy but guidelines (rough ones) would be excellent.

 

[JohnDTampaBay]

Here is a basic outline of the steps. You'll need to work through some of the dialog boxes on your own. They're not so difficult. Just take it one step at a time.

[ol][li]CREATE THE BASIC PACKAGE[/li]
[ol][li]Open a new package in designer[/li]
[li]Create a connection to each file. There are options for text file source and destination. Don't worry about the changing filename issue for now.[/li]
[li]Use the Transform Data task to set up the export from source connection to destination connection[/li]
[li]Test the export process until successful.[/li][/ol]
[li]ADD THE DYNAMIC PROPERTIES TASK[/li]
[ol][li]Follow the directions above to create the Dynamic Properties task for the changing filename.[/li]
[li]Add workflow so Dynamic Properties executes before the data export. Connect Dynamic Properties task to source connection.[/li]
[li]Test the entire package.[/li][/ol][/ol]

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

I think we may be getting confused, the DTS package is setup and working fine. I need to know how I add a query to change the destination\output filename.

The point of this is that I need to change the DTS package to get this result. I don't know what menu to access or property to change in order to do this so could really do with some instructions.

Thanks

 

[JohnDTampaBay]

We are chasing our tails, aren't we? Open the DTS package in design view. I envision two connections defined with one data transformation arrow pointing from source to destination. The Dynamic Properties task should be the last task on the button bar on the left side of the window. When the dialog box opens, click the Add button. How are we doing so far?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rzirpolo]

Hmmm yes I have already done that, I need to know where I set up the query in order to change the DESTINATION file name.

Guidelines in doing that so I can test it out.

Thanks

 

[rzirpolo]

My apologies, after hunting around I can see that I can select Query as an option for setting the Destination file name. I will have a test with this and get back to you if I have any further questions.

Most probably ha ha !!!

I appreciate all the help you've given so far

 

[rzirpolo]

Ok I'm stuck. I don't know what to put in for the Query. I have tried a number of things but it won't accept it.

Is a SELECT statement used ? Also how do I specify the full filename and also the location I want to output to in this one Query ?

An example would be great.

Thanks

 

[JohnDTampaBay]

The query is any valid SELECT statement against a connection you've set up. I believe the query should look something like this:

SELECT DISTINCT
    '\\Server\Directory\TRNFXFRM'  --File location & prefix
    + Field1
    + '.txt'
FROM [extf200030930.csv]

I'm have some issues querying the text file, not sure why. I've posted a question of my own. Perhaps someone else knows of a DTS limitation with Dynamic Properties.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.