Loading data from Object type variable to a table

Question

Loading data from Object type variable to a table

Amith Vemuganti

Ten Centuries

Points: 1350
More actions
July 23, 2008 at 2:20 pm

#122671

Hi All,
Could anyone please let me know how can I load the record set into a database table from an Object type variable in SSIS.
Any help would be appreciated.
Thanks in advance,
-Amith Vemuganti

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

why would you want to use an object variable as your source as opposed to using the sql that produced the recordset as your datasource through an OLE DB Source object in the Data Flow task? The object variable is good when you use the Execute SQL Statement to return the recordset and then loop through the recordset, via a For Each Loop for example, to then pass variable values to a Data Flow task for processing.

If you can provide a couple more details, that would be helpful in helping you out. Also, if you are doing it because you want to execute a stored procedure and then do a Data Flow task to eventually populate into a table, you setup an OLE DB source and have the SQL statement be the stored procedure call.

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 2

Thanks for the response. The main purpose of placing result set in Object type variable is to do some data validation. I want to populate the same data again into a table in different schema after validation process is finished. I thought if I can use the same result set which is there in the object type variable instead of querying the source tables again. I'm not sure how effective is this solution. Please advice.

Thanks,

-Amith Vemuganti

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 3

hmm, someone might have a better answer than this but a thought could be to insert the validated records directly into a table instead of returning into a recordset object variable. then, in the next data flow, read the table as your data source so you are not hitting the original source tables.

my preference would be to do the validation and processing of the original data in a data flow task and take that data and as the final piece of the data flow, insert the records into a destination object. but that would depend on your specific process that you are working with.

hth

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 4

I agree to your point Chuck. But I also want invalid records which I'm going to publish to a flat file.

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 5

in the data flow, you can setup a flow that will export the bad records to a flat file that fail the data validation.

for example using a conditional split transform can split the good records to one destination and the bad records to the flat file destination

born2bongo SSCrazy Points: 2477 More actions · Answer 6

On a similar theme, can anyone tell me how to write results held in variables to a different table?

I have a sql task that extracts a column of 3000 items, and a For Each Loop that works through the list, using a data flow taks to build a new table from each matching item.

The data flow task maintains a row count variable, and as a second step within the For Each loop I want to write the a record to a different table, giving the item from the list and the count of rows output for it.

I've tried using a data flow task, but this seems to fail because it doesn't have a source (it runs ok but doesn't write a row). I've also tried using a SQL task but this gave a variety or errors depending on what I fed to it. I've just read that Insert Into is not a suitable candidate for this task.

What can I use at the control flow level, inside the for each container, that will allow me to add a row to a table from the values of 3 variables?

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 7

for this scenario, i would probably do one or the other of the following:

the first thought would be to have a stored procedure on the server that does the insert with parameters in the stored procedure to accept the variable values and then use a Execute SQL task to call the stored procedure.

The second approach would be to use Expressions in the Execute SQL task and dynamically build your SQL statement from the variables in the Expression editor for the SQLStatementSource property.

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 8

Amith Vemuganti

Ten Centuries

Points: 1350

July 24, 2008 at 7:32 am

#847730

Thanks Chuck. Let me give a try and see.

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 9

Chuck, I tried the conditional split transform and its working good in splitting the data set.

One thing I noticed is, I was able to set condition only on some constant values. For Eg, column0 == "ABC". Could anyone tell me if I can split the rows to different destinations by setting up a condition that will validate the existence of column0 values in a database table.

For Example, Column0 NOT IN (SELECT a.Column0 FROM dbo.Table a)

Thanks,

-Amith Vemuganti

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 10

well, the conditional split only provides one input for data so what you can do is add a data source to the package and a merge join task and then left join on the original data and this conditional data source. bring in the data from the original and a field from the conditional source.

in the conditional split task, setup a condition where if the data is null in the conditional field, one set of data is created (the invalid dataset) and if not null another set of data is created (the valid data).

hth

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 11

Amith Vemuganti

Ten Centuries

Points: 1350

July 24, 2008 at 3:34 pm

#848119

Great, Thanks Chuck. Its working.