I have a table in a db which has 10 columns and its mapping to a table in a diff db which has 10 columns too... but,only 7 columns map from the source table and 3 other columns are getdate(), a date from the variable(which is passed from an external application) and the third is from a business rule which states if column 1 is '002' then insert category002 or something like that.how can i input these three columns into the destination?raghusai.k

If the Destination table has not these three columns you need to add them. Otherwise please explain your problem a little bit clearer, post some sample data and DDL.

Table Source Table Dest AGNT_CD----------------------- PRDCR_ID MTH_DT---------- MTH_DT PROD_ID--------- PROD_CD POL_QTY-------- POL_QTY NET_POL_QTY---- NET_POL_QTY POL_PREM_AMT--- POL_PREM_AMT NPF_PREM_AMT--- NET_PREM_AMT NPF_FRST_YR_COMM_AMT--- FRST_YR_COMM_AMT NPF_RNWL_COMM_AMT------ RNWL_COMM_AMT LST_UPDT_TS MSR_CD CYCLE_DT Hi Christa, i have given the column names for both source and destination.The three columns i was talking about were LST_UPDT_TS,MSR_CD and CYCLE_DT LST_UPDT_TS : this should be loaded with the datetime.now MSR_CD : This will be the business rule depending on the Prod_cd value (If prod_cd = 2 then i need to insert category 2 or something like that) CYCLE_DT: this will be a variable value which is passed to the application from a c# code . Let me know if you need any more details.. raghusai.k

You'll just need to add a Derived Column transform to "create" the columns. Add an LST_UPDT_TS column, using GETDATE() as an expression. Add an MSR_CD column, using an expression with the conditional operator - something like this: (PROD_CD == "002") ? "Category 2" : "Other Category" Add a CYCLE_DT column, using the variable as the expression. Now - you haven't said you know how to "pass in" a value from your C# app, in fact you haven't said how you're calling the package. Are you using the API or command line? If you're using the command line, you can push in variable values on the command line - look at DTEXEC's parameters. Talk to me now on

Hi todd, Thanks for the reply,we haven't figured out whats the best way to pass the Cycle_DT to the variable.But,the whole system has no manual intervention.Can you propose something?and one more question? I have a char value 201012 which shd be converted as datetime value at destination.how to do that?raghusai.k

to the second question: if you want to convert the value to datetime or dat you hav to add a day, 01 or last day of month (more difficult): create a new column in Derived Column in Expression add "01" + "/" + right(MyValue,2) + "/" + substring(MyValue,1,4), change the type to db_datetime. May you have to change place of month and day, depending on your settings. to the first question: If you have no chance to do it while starting the package, what about a Configuration table or another table in SQL Server? What kind of value is Cycle_DT?

Thanks,christa.. I am using 1st day of the month but i still have a error: which is a truncation error,this is how my columns are defined in source and destination: Source char(6) Destination datetime(notnull)raghusai.k

try to change the year, month and day. Sometimes it has as bit of a puzzle to find the right style. And the datatype in Dervied Column has o change to dt_dbdate or dt_dbtimestamp.

You can pass values into an SSIS variable via the command line. Look up the /SET argument on DTEXEC. Here's a post on converting strings to dates. Talk to me now on

Todd,i figured out the Date conversion thing.But,is there a way to pass values to ssis variables other than command line?raghusai.k

What kind of value are we talking about? I already mentioned two possible ways to do that

Value is in the format "yymm" and should also pass file absolete path say "C://myfolder\abcdyymm.txt" these two values have to be passed from an external application in to an SSIS variableraghusai.k

Todd,i figured out the Date conversion thing.But,is there a way to pass values to ssis variables other than command line? raghusai.k you can also provide values for SSIS packages through DTExecUI, open run, and enter dtexecui there, in the DTEXECUI, select the package, and then you can set variable values in appropriate tabhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

What kind of "way" are you looking for? How is your C# app invoking the package? That will determine what a good way to pass variables is. Wide open, you could use package configurations, and have the app modify the XML config file or the SQL config table before executing the package. You could also have the package (manually) read a table or file (non-package config) that the C# app sets up appropriately before invoking it. Talk to me now on