Coverting SSIS object variable to Long data type
I need suggestion for one of issues - I have a variable in SSIS package of type Object - I need to pass this value as DataType Long to a stored procedure - To typecast this I tried to check for Derived Column or Data Conversion in Data Flow tasks - Both Derived Column or Data Conversion in Data Flow tasks need an input which cannot be a SSIS variable - I am going to use script task to assing using C# and store the returned type and send it to procedure Please let me know for any better approach for this scenario "It is not enough to aim, you must hit."
August 5th, 2011 4:16am

first: why you used Object type variable? did you fetched a full result set containing many data rows into that object type variable? Object type variable in SSIS is just for storing more than one data rows into a variable , not for single value. if you want to store single value you should use other types than object. But if you stored whole data stream into object type variable, you should loops through that variable with foreach loop container with enumerator as ado enumerator. It is much better to tell us what exactly you trying to do and we can help you better.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
Free Windows Admin Tool Kit Click here and download it now
August 5th, 2011 4:32am

1) Can you provide more detailed flow ? It is required to know why the variable is object data type/ can it be changed to Long data type directly ? 2) Yes, you are right, derived / data conversion transformation cannot take variable as input. 3) The only way is what you told: in a script task convert the data type : http://msdn.microsoft.com/en-us/library/kx3x7f55.aspx 4) Now , as what Reza said , the object variable can hold multiple values . If you are doing so, you could do an enumeration using for each loop and convert one by one , but this also requires the C# code. 5) The conversion cannot be achieved even by the SSIS expressions for object variable. You could try casting it in the variable properties by setting EvaluateAsExpression property to true and entering the expression. Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
August 5th, 2011 5:39am

> Thanks you are right. The scenario involves looping through records table and perform operation > In any case if there is error it need to capture current parameter / record details > Pardon my ignorance on use of object list. I'm clear on this."It is not enough to aim, you must hit."
Free Windows Admin Tool Kit Click here and download it now
August 5th, 2011 10:36am

> Thanks you are right. The scenario involves looping through records table and perform operation > In any case if there is error it need to capture current parameter / record details > Pardon my ignorance on use of object list. I'm clear on this. "It is not enough to aim, you must hit." so why you don't use object variable? I can't understand your reason. and what you want to do with each record? maybe your requirement could be fulfilled within the data flow task. if you do in data flow you can capture error rows with CONFIGURE ERROR OUTPUT if you do in control flow you can use precedence constraint to capture error parameters. so , first of all let us know what exactly you want to do with each data row in table ?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
August 6th, 2011 3:42pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics