 | |
Dynamic DTS
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Dynamic DTS -
05-16-2005
, 08:24 AM
|
Sure How can I change the filename for a text file connection? (http://www.sqldts.com/default.aspx?200) "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: Yes, all your statements are true and make sense. The problem is the the variables are not being passed to ALL the steps of the task. Steps one (dynamic properties ) and two (execute SQL task) work with /A values. Step three (text file source linked to a SQL server connection by a transform data task) does not work. Step four (execute SQL task) works. I know step three is not working because the date being loaded is appended (duplicated) onto the end of the data in the table instead of the different data file being loaded to the second database. That would mean it used the design values instead of the passed /A paramaters. Can you point me to a sample active script that would perform step three? "Allan Mitchell" wrote: Ok So you have some variables inside your package that you set from outside using the /A switch. These in turn should set properties of your package. I personally wouldn't use the DP task I would use an Active Script task to do the assignments but both should be fine When you reopen your package the value you see in the design time will be your design time value not the value you pass at runtime. It will not change unless you saved the package back with the value. Make sense. Allan "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: The reason for the variable is because I have different servers/database and input file names for different customers. The file format/database layouts are identical for each customer. I was wanting to execute the DTSRUN by batch with the passed variables so I did not have to change each DTS before the run. Setup the DTS and run it 20 times to 20 different databases with 20 different input files. "Allan Mitchell" wrote: You can he set the DataSource property of the Text File Connection to be your new value and you can set the Catalog property of the SQL Server object to be your database and the DataSource property to be the server. If you are using the DP like this then there is little point in going through a variable first unless you need it later. "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: That was during runtime. Digging deeper, the batch execution has three set commands for the variables. It appears the dynamic properties and the two execute SQL task are working against the databases set in the variables. That puts me back the last statement of the original post. In the text file source how do you use the CSV variable instead of a "filename" and for the SQL server connection how do you use the SQL variable for Server and the DB variable for the Database? These same problems are also inside the transform data task on the source and destination tabs. "Allan Mitchell" wrote: Is that when you look at what it does in the runtime or when you look at things after the package has finished. Have you checked to make sure things are being picked up? Allan "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: My order is dynamic properties to set CSV, SQL, DB global variables. On success, an execute SQL task to clear the tables. On success, a text file source linked to a SQL server connection by a transform data task . On success, an execute SQL task to load the foreign keys to the table. The dynamic properties appear not to change because it always references the default values entered when the variables were defined. "Allan Mitchell" wrote: I am not really sure what you have done here but so long as the Dynamic Properties task is the first thing to go in the package it should be fine. In the DP task you can assign various externally sourced values into properties of various objects in your package. Are you saying this is not happening? "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: In am importing various delimited files in to different servers/databases. The DTS has dynamic properties, a execute SQL task to clear the tables, a text file source, a transform data task, a SQL server connection, and a execute SQL task to load the foreign keys to the table. I have included the dynamic properties and set Global variables of CSV, SQL, & DB. When you execute the file thru DTSRUN the variables are not reset by the command inside the batch file, but always use the default values. Also, for the text file source how do you use the CSV variable instead of a "filename"? For the SQL server connection how do you use the SQL variable for Server and the DB variable for the Database? These same problems are also inside the transform data task on the source and destination tabs. |
#12
| |||
| |||
|
|
I have used you 200, 201, & 241 as my examples. The thing still does not work. CSV, SQL, DB are defined (with defaults) in dynamic properties My active script: Function Main() Dim oConn Set oConn = DTSGlobalVariables.Parent.Connections("FIN_Fund") oConn.DataSource = DTSGlobalVariables("SQL").Value oConn.Catalog = DTSGlobalVariables("DB").Value Set oConn = Nothing Dim iConn Set iConn = DTSGlobalVariables.Parent.Connections("FundIN") iConn.DataSource = DTSGlobalVariables("CSV").Value Set iConn = Nothing Main = DTSTaskExecResult_Success End Function My Batch File: set SQL=GTWSQLTEST set DB=walhrfin CALL dtsrun /N LoadFS /F LoadFS /L LoadFS.txt /W-1 /A "DB":"8"="walhrfin" /A "SQL":"8"="gtwsqltest" /A "CSV":"8"="e:\fs.csv" The two Execute SQL are still working. The transformation in the middle is still using the default values for the Global Variables. "Allan Mitchell" wrote: Sure How can I change the filename for a text file connection? (http://www.sqldts.com/default.aspx?200) "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: Yes, all your statements are true and make sense. The problem is the the variables are not being passed to ALL the steps of the task. Steps one (dynamic properties ) and two (execute SQL task) work with /A values. Step three (text file source linked to a SQL server connection by a transform data task) does not work. Step four (execute SQL task) works. I know step three is not working because the date being loaded is appended (duplicated) onto the end of the data in the table instead of the different data file being loaded to the second database. That would mean it used the design values instead of the passed /A paramaters. Can you point me to a sample active script that would perform step three? "Allan Mitchell" wrote: Ok So you have some variables inside your package that you set from outside using the /A switch. These in turn should set properties of your package. I personally wouldn't use the DP task I would use an Active Script task to do the assignments but both should be fine When you reopen your package the value you see in the design time will be your design time value not the value you pass at runtime. It will not change unless you saved the package back with the value. Make sense. Allan "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: The reason for the variable is because I have different servers/database and input file names for different customers. The file format/database layouts are identical for each customer. I was wanting to execute the DTSRUN by batch with the passed variables so I did not have to change each DTS before the run. Setup the DTS and run it 20 times to 20 different databases with 20 different input files. "Allan Mitchell" wrote: You can he set the DataSource property of the Text File Connection to be your new value and you can set the Catalog property of the SQL Server object to be your database and the DataSource property to be the server. If you are using the DP like this then there is little point in going through a variable first unless you need it later. "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: That was during runtime. Digging deeper, the batch execution has three set commands for the variables. It appears the dynamic properties and the two execute SQL task are working against the databases set in the variables. That puts me back the last statement of the original post. In the text file source how do you use the CSV variable instead of a "filename" and for the SQL server connection how do you use the SQL variable for Server and the DB variable for the Database? These same problems are also inside the transform data task on the source and destination tabs. "Allan Mitchell" wrote: Is that when you look at what it does in the runtime or when you look at things after the package has finished. Have you checked to make sure things are being picked up? Allan "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: My order is dynamic properties to set CSV, SQL, DB global variables. On success, an execute SQL task to clear the tables. On success, a text file source linked to a SQL server connection by a transform data task . On success, an execute SQL task to load the foreign keys to the table. The dynamic properties appear not to change because it always references the default values entered when the variables were defined. "Allan Mitchell" wrote: I am not really sure what you have done here but so long as the Dynamic Properties task is the first thing to go in the package it should be fine. In the DP task you can assign various externally sourced values into properties of various objects in your package. Are you saying this is not happening? "goinesj (AT) paec (DOT) org" <goinesjpaecorg (AT) discussions (DOT) microsoft.com> wrote in message news:goinesjpaecorg (AT) discussions (DOT) microsoft.com: In am importing various delimited files in to different servers/databases. The DTS has dynamic properties, a execute SQL task to clear the tables, a text file source, a transform data task, a SQL server connection, and a execute SQL task to load the foreign keys to the table. I have included the dynamic properties and set Global variables of CSV, SQL, & DB. When you execute the file thru DTSRUN the variables are not reset by the command inside the batch file, but always use the default values. Also, for the text file source how do you use the CSV variable instead of a "filename"? For the SQL server connection how do you use the SQL variable for Server and the DB variable for the Database? These same problems are also inside the transform data task on the source and destination tabs. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.