 | |
trying to automate a process we perform manually
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: trying to automate a process we perform manually -
09-22-2008
, 09:49 AM
|
Intuitive to you maybe, it's Greek to me!! hey, you got it work or what?  before you get to far ahead of me, remember I first need to automate the process of bringing in the downloaded file into the Access table in the first place. Ok then the DTS should: - FlatFile ---> AccessTable - AccessTable (or flatFile) ---> SQLServer Table I've finally muddled through using Text File as my Source Connection and Access as my destination using the Transform Data Task. The file is fixed and I set all the columns, still confused on the Transactions tab and how to setup the source columns to the destination columns. Maybe you mean Transformation tab... Anyway, here how you should build it up: The grey arrow shows you that there is a data flow between the 2 connections. The source/destination column's links are automatically set by DTS designer. So if the File's columns and the Access Table's columns are exaclty the same you shouldn't need to modify the trasformations. If you need to skip one column or set up a different trasformation (let's say to add or change records) then you should modify what DTS set up for you. You shouldn't care by now of the other 2 tabs (lookups and options). One thing I don't like of this automate process is that it sets as many transformations as the columns' number is (a trasformation is the arrow that link source columns to destination column in Trasformations tab of the Trasformation Data task). If my columns share the same trasformation type (say, every column must be lowercased...) I normally prefer to group all of my columns in a single trasformation. To do this click Select All and delete current trasformations; then select all sources and all destinations and click New. A window will appear asking you to choose Trasf Type. Select Copy column and click OK. Then double-click on the arrow (newly appeared) and in the first tab click properties. Depending on which trasformation type has been chosen before, it will open a window to configure it. For Copy Column trasf type then you only need to link source and dest columns. Same behaviour for Access-->SQLServer trasformation type. The problem of the access becoming bigger... maybe this could be caused by the (large) data pump processed. so fro every operation it logs transaction performed on its tables. So i guess that this logging can be different if the operations are done manually (how?) or with external programs. If you compare the 2 dimensions (the first done manually, the second done with DTS) after having compacted i see no reason why the second must be much larger than the first. Let me know, M. Anyways, when I execute it seems to work. Only problem is that my Access file is much larger than when I do this stuff manually. Need to figure out why this is so. |
#32
| |||
| |||
|
|
Intuitive to you maybe, it's Greek to me!! hey, you got it work or what? before you get to far ahead of me, remember I first need to automate the process of bringing in the downloaded file into the Access table in the first place. Ok then the DTS should: - FlatFile ---> AccessTable - AccessTable (or flatFile) ---> SQLServer Table I've finally muddled through using Text File as my Source Connection and Access as my destination using the Transform Data Task. The file is fixed and I set all the columns, still confused on the Transactions tab and how to setup the source columns to the destination columns. Maybe you mean Transformation tab... Anyway, here how you should build it up: The grey arrow shows you that there is a data flow between the 2 connections. The source/destination column's links are automatically set by DTS designer. So if the File's columns and the Access Table's columns are exaclty the same you shouldn't need to modify the trasformations. If you need to skip one column or set up a different trasformation (let's say to add or change records) then you should modify what DTS set up for you. You shouldn't care by now of the other 2 tabs (lookups and options). One thing I don't like of this automate process is that it sets as many transformations as the columns' number is (a trasformation is the arrow that link source columns to destination column in Trasformations tab of the Trasformation Data task). If my columns share the same trasformation type (say, every column must be lowercased...) I normally prefer to group all of my columns in a single trasformation. To do this click Select All and delete current trasformations; then select all sources and all destinations and click New. A window will appear asking you to choose Trasf Type. Select Copy column and click OK. Then double-click on the arrow (newly appeared) and in the first tab click properties. Depending on which trasformation type has been chosen before, it will open a window to configure it. For Copy Column trasf type then you only need to link source and dest columns. Same behaviour for Access-->SQLServer trasformation type. The problem of the access becoming bigger... maybe this could be caused by the (large) data pump processed. so fro every operation it logs transaction performed on its tables. So i guess that this logging can be different if the operations are done manually (how?) or with external programs. If you compare the 2 dimensions (the first done manually, the second done with DTS) after having compacted i see no reason why the second must be much larger than the first. Let me know, M. Anyways, when I execute it seems to work. Only problem is that my Access file is much larger than when I do this stuff manually. Need to figure out why this is so. |
#33
| |||
| |||
|
|
Intuitive to you maybe, it's Greek to me!! hey, you got it work or what? before you get to far ahead of me, remember I first need to automate the process of bringing in the downloaded file into the Access table in the first place. Ok then the DTS should: - FlatFile ---> AccessTable - AccessTable (or flatFile) ---> SQLServer Table I've finally muddled through using Text File as my Source Connection and Access as my destination using the Transform Data Task. The file is fixed and I set all the columns, still confused on the Transactions tab and how to setup the source columns to the destination columns. Maybe you mean Transformation tab... Anyway, here how you should build it up: The grey arrow shows you that there is a data flow between the 2 connections. The source/destination column's links are automatically set by DTS designer. So if the File's columns and the Access Table's columns are exaclty the same you shouldn't need to modify the trasformations. If you need to skip one column or set up a different trasformation (let's say to add or change records) then you should modify what DTS set up for you. You shouldn't care by now of the other 2 tabs (lookups and options). One thing I don't like of this automate process is that it sets as many transformations as the columns' number is (a trasformation is the arrow that link source columns to destination column in Trasformations tab of the Trasformation Data task). If my columns share the same trasformation type (say, every column must be lowercased...) I normally prefer to group all of my columns in a single trasformation. To do this click Select All and delete current trasformations; then select all sources and all destinations and click New. A window will appear asking you to choose Trasf Type. Select Copy column and click OK. Then double-click on the arrow (newly appeared) and in the first tab click properties. Depending on which trasformation type has been chosen before, it will open a window to configure it. For Copy Column trasf type then you only need to link source and dest columns. Same behaviour for Access-->SQLServer trasformation type. The problem of the access becoming bigger... maybe this could be caused by the (large) data pump processed. so fro every operation it logs transaction performed on its tables. So i guess that this logging can be different if the operations are done manually (how?) or with external programs. If you compare the 2 dimensions (the first done manually, the second done with DTS) after having compacted i see no reason why the second must be much larger than the first. Let me know, M. Anyways, when I execute it seems to work. Only problem is that my Access file is much larger than when I do this stuff manually. Need to figure out why this is so. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.