ftp command help

I need to schedule copying of files to an ftp share using a SQL agent
job..but i dont know how to do that in one step
Say for eg: the source of files are on \\computerA\share1\*.*
and the destination is an ftp site ftp.x.com with a username and password
And i want to run this job on say computerB.
Then what would be the command ?
ThanksYou can either
1) create a DTS package (FTP task) then schedule the job by using
'dtsrun' command in the job.
2) use native ftp cmds in the job (jobs steps in an order). e.g.
ftp ftp.x.com
cd (to the location)
put <file>
dir (check if the file there)
close (close the connection)
bye
Mel

ftp command help

I need to schedule copying of files to an ftp share using a SQL agent
job..but i dont know how to do that in one step
Say for eg: the source of files are on \\computerA\share1\*.*
and the destination is an ftp site ftp.x.com with a username and password
And i want to run this job on say computerB.
Then what would be the command ?
ThanksYou can either
1) create a DTS package (FTP task) then schedule the job by using
'dtsrun' command in the job.
2) use native ftp cmds in the job (jobs steps in an order). e.g.
ftp ftp.x.com
cd (to the location)
put <file>
dir (check if the file there)
close (close the connection)
bye
Mel

Frustrations With Data Conversion Task

It seems to me, after some frustrations, that Destination components (and perhaps the Source components as well) should have included data conversation facilities as part of their designer/UI; being the experts on their data requirements. In particular, for the Destination components, data conversion could have simply been an extension of their record mapping activity.

Presently, I am force to seperately (1) note the datatypes, then (2) convert them in the Conversion component, then (3) map them in the Destination component, then (4) cuss...(think a lot of fields now and going forward)...

A seperate Data Conversion task might still be useful for completeness, but generally data conversation is only meaninful when you are directly accessing a datastore.

I am curious as to why data conversation features was not embedded in the Destination component (e.g. in its superclass)? Perhaps I am missing a subtle point.

Please provide more details as to your issue.

Derived columns can convert data. As can the Data Conversion components.|||

G1 wrote:

Clearly the Destination component and perhaps the source component should offer data conversation as part of their design (ie. GoF expert pattern)

I am curious as to why this was not done?

Can you explain a little more?

What is a data conversation?

What is "the destination component"? There are many destination components.

What is "the source component"? There are many source components.

What does GoF stand for?

-Jamie

|||See re-edited version (during your reply) above...|||

G1 wrote:

See re-edited version (during your reply) above...

Can I ask one simple question...are you using the word "conversation" correctly or is it a typo where you actually meant to say "conversion"?

-Jamie

|||

Typo.

I have since edited my post for clarity. Unfortunately, even as I was doing so you were responding to it.

Nontheless, thanks for your quick responses.

|||

I simply don't understand. Data sources and destinations SHOULD NOT handle data conversion. If that were the case, the whole notion of ETL wouldn't hold water. The idea is to pull from a source, transform the data, and load it into the destination as fast as possible. You don't want to hold up processing into a destination just to convert data.

Use a derived column or the data conversion components to suit your needs.

I don't understand the part about "noting" the datatypes. You already know them, otherwise you wouldn't know that you need to convert them. Personally, I never use the data conversion component. I always cast my fields in a derived column component.

|||

Phil Brammer wrote:

I simply don't understand. Data sources and destinations SHOULD NOT handle data conversion. If that were the case, the whole notion of ETL wouldn't hold water. The idea is to pull from a source, transform the data, and load it into the destination as fast as possible. You don't want to hold up processing into a destination just to convert data.

Use a derived column or the data conversion components to suit your needs.

I don't understand the part about "noting" the datatypes. You already know them, otherwise you wouldn't know that you need to convert them. Personally, I never use the data conversion component. I always cast my fields in a derived column component.

I agree 100%. The purpose of the source adapter(s) is to make data available to the pipeline for transformation. Nothig else. Each component does something very specific, and very atomic, that is what makes it possible to build complicated business logic.

-Jamie

|||

Originally, my anks was with the fact that the Data Conversion component does not provide the data destination field types during its type selection. Without this nice bit of information, I had to flip back and forth to ensure the exact matching required by the Destination component (ie. Unsigned 1-Byte to Unsigned 2-Byte).

Downstream, another annoyance is that the pre-converted fields remain alphabetically mixed-in with the newly converted fields, cluttering the drop-down lists. While availability of pre-converted versions can be useful at times, their presence in this way is a source of mistakes. There should be some other way of managing it all; it is Data Conversion component not Copy Column (which I've not tried), after all.

Nevertheless, I am more convinced now that data conversion belongs at the datastore interface. Examples of this architecture brings to mind Excel, Word, DataAdapter, Serialization, Paint, etc. all of which read and write a growing list of source/destination datastore formats while utilizing a single internal data format to apply their growing list of functionality.

Incidentally, with respect to Phil Brammer's question, I did not already memorize the data types of all 120 fields averaged across 80 tables

I hope this is clearer. Thanks for you replies.

-- George

PS. Don't get me wrong, ETL is a better conceptual model than DTS...by far

|||

G1 wrote:

Originally, my anks was with the fact that the Data Conversion component does not provide the data destination field types during its type selection. Without this nice bit of information, I had to flip back and forth to ensure the exact matching required by the Destination component (ie. Unsigned 1-Byte to Unsigned 2-Byte).

So you want to be able to see the metadata of the destination when you are defining your data conversions? Yes, I can see why this would be useful if you have alot of columns. Though I can't see how your original post relates to it. But that's a dogression...

Good point well made.

G1 wrote:

Downstream, another annoyance is that the pre-converted fields remain alphabetically mixed-in with the newly converted fields, cluttering the drop-down lists. While availability of pre-converted versions can be useful at times, their presence in this way is a source of mistakes. There should be some other way of managing it all; it is Data Conversion component not Copy Column (which I've not tried), after all.

Yes, many people find the ordering of columns in the UIs annoying. Answer me this though. What is the correct ordering? This is the sort of information that Microsoft would want and if you ask 10 people you might get ten different answers. See the problem here?

G1 wrote:

Nevertheless, I am more convinced now that data conversion belongs at the datastore interface. Examples of this architecture brings to mind Excel, Word, DataAdapter, Serialization, Paint, etc. all of which read and write a growing list of source/destination datastore formats while utilizing a single internal data format to apply their growing list of functionality.

Comparing SSIS with those tools is not comparing apples with apples. its an irrelevant comparison in my opinion.

And Paint? What has Paint got to do with this?

G1 wrote:

Incidentally, with respect to Phil Brammer's question, I did not already memorize the data types of all 120 fields averaged across 80 tables

I hope this is clearer. Thanks for you replies.

-- George

PS. Don't get me wrong, ETL is a better conceptual model than DTS...by far

DTS is an ETL tool. SSIS is an ETL tool. I think what you meant to say was "SSIS is a better conceptual model than DTS"

-Jamie

|||

"Yes, many people find the ordering of columns in the UIs annoying. Answer me this though. What is the correct ordering?"

In this case, I don't think there is such a thing as "correct" ordering. However, I would like more complexity. For example, (1) multiple columns in the field list that include such attributes as data type, size, record sequence, etc; (2) auto column sizing; (3) user selectable column sorting; and (4) auto hiding of pre-converted fields, possibly as expandable entries beneath their converted selves similar to tags in XML Viewer. In any event, the current field listing ignores the need for such complexity.

"Comparing SSIS with those tools (Excel, Word, DataAdapter, Serialization, Paint) is not comparing apples with apples. its an irrelevant comparison in my opinion."

The similarity of SSIS to these tools is that they all "import -- manipulate -- export" their data. And like them, SSIS extracts/imports and loads/exports a variety of datatypes/formats. And once imported permits a variety of manipulations on them, independent of their source datatypes. For example, Char(N) and NText are both treated as strings and 1-Byte Signed and 4-Byte Signed are both treated as Integers, etc. In fact, in most cases the underlying datatype is only relevant at the interface to the datastore.

There may be a benefit to having a distinct Data Conversion component; however, the current one could be refined. As well, the Data Destination component (and possibly the Data Source component) should be enhanced to included an embedded instance of their own version of this improved Data Conversion component.

George.

|||

G1 wrote:

There may be a benefit to having a distinct Data Conversion component; however, the current one could be refined. As well, the Data Destination component (and possibly the Data Source component) should be enhanced to included an embedded instance of their own version of this improved Data Conversion component.

George.

No, I'll go down to my grave disagreeing with you on this point. See my posts above for why. Jamie's too.

|||

Phil Brammer wrote: "I simply don't understand. Data sources and destinations SHOULD NOT handle data conversion. If that were the case, the whole notion of ETL wouldn't hold water. The idea is to pull from a source, transform the data, and load it into the destination as fast as possible. You don't want to hold up processing into a destination just to convert data."

I agree with you up to a point.

The thing is, conceptual seperation and physical seperation are two different things. Physical proximity is often convenient which is the point of the proposed embedded data conversion component. Performane, however, is not a concern since the conversion component is created only as needed.

On the other hand, the availability of conversion within the datastore access components could increase ease of use as mentioned previously and even effeciency by, among other things, making it easier to limit the collection of data types that participate in the transformation process proper. In particular, to allow a pattern of use wherein "exported" and/or "pre-loaded" data is converted at the interface to the datastore, instead of arbitrarily throughout the transformation process. In any event, I expect most existing conversions already occur just before or after such datastore access or would if it weren't for the Data Conversion component's field clutter issue mentioned previously .

With respect to the persuite of performance, one of the things I've come to appreciate is that "ease of use" SHOULD be part of the performance equation. Often, performance as the naked goal results in tools that are conceptual nightmares (not the case with SSIS); like a sharp knife without a handle, such components/systems become a pain to use, apply, refine, etc.

As for going down to your grave disagreeing, go ahead . Your way may be equally right. Yes, you heard me. We both may have equally viable self-consistent frameworks or conceptual models justifying our tastes, expectations, and so forth.

George.

Frustrated: Error Output in Source and Destination Editor.

Hi

Is there anyway I can multi select rows when I'm changing the value for either Error or Truncation? I'm finding it frustrating having to change 2 values for each row in the output. for example redirect row, when I have 10,20,30 columns.

It would have been nice to have a global drop down at the top of each column so that I could then selectively change rows back.

Any hints and tips on making this easier would be gratefully appreciated.

ThanksThe global drop down that you're looking for is at the bottom of the Configure Error Output page, above the buttons, rather than at the top. You select multiple rows and columns, select the option that you want from the drop down list (eg Redirect row), and click Apply.

(I don't know whether this feature may be a recent addition. If so, you will see it at the latest in the next CTP.)

-Doug|||Hi doug,

I'm using The June CTP downloaded from MSDN and that doesn't have aany buttons. What release are you running?

And thankyou for your reply

Marcus|||This functionality was added after the June CTP, it should be available in a future CTP.

thankssql

from not shared to shared

hi all,
I am not being able to sort this out: if by a terrible lack of reason i forgo the chance to put the tick in the Make this data source a shared data source while going thru the Report wizard, is it possible to come back to this decision? And if so, how?

I went through all the menus and i did not find anything; i see data sets, i can add new data sets, i can add new data sources if i want, but how do i change my original data source from whatever it is now to shared, that i don't see. Simply stated, my question would be, how do I edit the properties of my current data source,

thanks,
kowalskyTry this? Create a shared datasource, view the datasource in notepad to gather the ID and name, and then change the RDL file in notepad to replace the local datasource with the shared datasource.

Fresh DBA doubts - Help appreciated

Hi,
1. How do I move data from a table along with the Table
stucture into another Database with same structure as
source database except the table to be moved.
2. How do i ask SQL Server Agent to backup to a Network
Drive.
3. one of my colleague suggests this backup plan. nighty
full backups, differential backups every four hours
between fulls, and transaction log backup every 1 hour in
between, What should be the sequence of Restoration in
case of failure. Also any other better solution with
Full/Differential/Transactional combination?
Regards
Chip1. DTS.
2. Ensure that your SQL Server Agent startup account has access to a
network share, and use the UNC naming convention in your backup statement.
3. Depends on how much data you are willing to lose and the amount of time
you are allowed to restore a database. In your current setup, you will lose
at most 1 hour's worth of work. Is this acceptable? For other
considerations, read the Data Recovery Best Practices document at
http://www.lumigent.com/solutions/papers.htm.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"chip" <anonymous@.discussions.microsoft.com> wrote in message
news:1ecfe01c4573f$3271c710$a301280a@.phx.gbl...
> Hi,
> 1. How do I move data from a table along with the Table
> stucture into another Database with same structure as
> source database except the table to be moved.
>
> 2. How do i ask SQL Server Agent to backup to a Network
> Drive.
>
> 3. one of my colleague suggests this backup plan. nighty
> full backups, differential backups every four hours
> between fulls, and transaction log backup every 1 hour in
> between, What should be the sequence of Restoration in
> case of failure. Also any other better solution with
> Full/Differential/Transactional combination?
> Regards
> Chip|||Sytax for #2 would be
backup database prod to disk = '\\fileserver\fileshare\prod.bak' with init
Make sure that the NT login that SQL Server and SQL Agent use have
permissions on the file share.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chip" <anonymous@.discussions.microsoft.com> wrote in message
news:1ecfe01c4573f$3271c710$a301280a@.phx.gbl...
> Hi,
> 1. How do I move data from a table along with the Table
> stucture into another Database with same structure as
> source database except the table to be moved.
>
> 2. How do i ask SQL Server Agent to backup to a Network
> Drive.
>
> 3. one of my colleague suggests this backup plan. nighty
> full backups, differential backups every four hours
> between fulls, and transaction log backup every 1 hour in
> between, What should be the sequence of Restoration in
> case of failure. Also any other better solution with
> Full/Differential/Transactional combination?
> Regards
> Chip|||Thanks to you both for the inputs.
Regards
Chip
>--Original Message--
>1. DTS.
>2. Ensure that your SQL Server Agent startup account has
access to a
>network share, and use the UNC naming convention in your
backup statement.
>3. Depends on how much data you are willing to lose and
the amount of time
>you are allowed to restore a database. In your current
setup, you will lose
>at most 1 hour's worth of work. Is this acceptable? For
other
>considerations, read the Data Recovery Best Practices
document at
>http://www.lumigent.com/solutions/papers.htm.
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Try MiniSQLBackup
>
>"chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ecfe01c4573f$3271c710$a301280a@.phx.gbl...
>> Hi,
>> 1. How do I move data from a table along with the Table
>> stucture into another Database with same structure as
>> source database except the table to be moved.
>>
>> 2. How do i ask SQL Server Agent to backup to a Network
>> Drive.
>>
>> 3. one of my colleague suggests this backup plan. nighty
>> full backups, differential backups every four hours
>> between fulls, and transaction log backup every 1 hour
in
>> between, What should be the sequence of Restoration in
>> case of failure. Also any other better solution with
>> Full/Differential/Transactional combination?
>> Regards
>> Chip
>
>.
>

Free Tutorial Source with from A to Z examples

Hi

I read alot of theoretical explanation of Notification Services.
I need a source for examples that explain Notification Services from A to Z.

Thank you.
Bishoy

Hi Bishoy -

Here's a copy/paste from my reply to your newsgroup posting.

I'd defiinitely suggest going through the walkthrough example included
in BOL as a good starting point. Additionally, examining the other
sample apps included in would be beneficial.

Beyond that, Shyam Pather has a book on the subject that is really
quite comprehensive. I've got one out there as well; mine is designed
to get you up to speed on SQLNS very quickly.

As for other resources, I haven't found anything specifically devoted
to SQLNS, only a few article here and there on the subject - some on
MSND, some on blogs, some on community sites.

I regularly blog on the topic (www.sqlns.com) and occasionally write
articles for SQL Server Central and Red-Gate's Simple-Talk sites.

BTW - here's an article that was adapted from on of the chapters of my
book.

http://sqlns.blogspot.com/2005/11/sqlns-architecture-article-on.html

Hope this helps.