How to verify constraints after a bulk ? (sql server destination)

Hello

I have four tables as xml. I'm successfully bulk loading them into 4 tables, using the SQL Server Destination, with check constraints unchecked.

The process manages to load all the data without any FK issue.

After that, how can I check the constraints ? (I've read a bit about is_not_trusted and sys.check_constraints)

ThibautHere are my conclusions so far (after googling more, checking this) etc..

It seems that I can establish back the foreign keys and check constraints by doing this on the tables:

alter table MyTable with check check constraint all

The double check will reenable the constraint and check existing data.

I came up with this request to find all the check constraints which are not trusted anymore:

select myschema.name as schema_name, mytable.name as table_name, myconstraint.name as constraint_name
from sys.check_constraints as myconstraint
inner join sys.objects as mytable on mytable.object_id = myconstraint.parent_object_id
inner join sys.schemas as myschema on myschema.schema_id = myconstraint.schema_id
where myconstraint.is_not_trusted = 1

Same applies to foreign keys by selecting from sys.foreign_keys...

Is there any caveat with the approach ?

Thibaut|||Cannot find any caveat for the moment.

The check check constraint all properly detects any error in the loaded data, and the constraints and foreign keys seems to go back to is_not_trusted = 0, just like expected.sql

How to use variables in OLE DB or OLE Destination?

Hello -

I am making good progress with my ssis package. However, there is one new thing which I cannot graps yet. That is, how to use variables when I want to update or insert a new row. I have some columns in my tables that require the datetime that the update/insert occured, the person making the change, and a few other things that are not part of the incoming data source (an excel file).

I created some user variables for these things, but I cannot figure out how to use them with my OLE DB Command and OLE DB Destination. One handles Inserts and the other handles the Updates based on whether a row in the Excel file is new (an Insert) or already exists (an update). Along with the insert or update, I'd like to set the Lastupdate, Who, etc.

Thanks for any help

- will

Use a Derived Column transform, add a new column, and use the variable as the expression. This column will now contain your variable value. It can now be used in either the OLE-DB Destination or Command just like any other column, so you can insert or update the target column as required.|||

For the LastUpdated column you should consider using a default value on the column definition. Place GetDate() in the textbox beside Default.

Otherwise, do what Darren said and use a Derived Column, and put the variable in the Expression textbox.

|||Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].|||

DarrenSQLIS wrote:

Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].

Not to mention there is no simple "DATE" type yet in SQL Server. So if you do use the getdate() function, you'll get the timestamp portion as well, which won't be unique for all rows processed in that batch. Do what Darren suggests, and use a system variable, or a variable that's been converted to a simple date prior to execution. You certainly do not want to evaluate getdate() for each row if you don't need to.|||

Well it depends on the requirements...

Do you need a consistent value for ALL rows in a load?

This sounds like an audit field so what other applications are using it? If a single user inserts or updates a record in addition to the data loads, and you want consistent behavior across your application then setting a default value for the column in the table definition makes sense. We've used GetDate() as the default value for LastUpdatedDate on many projects without any adverse effects on performance.

|||

Thanks to everyone for the advice. The idea of using the derived column transform sounds great. I have toyed with the idea of whether to use a consistent date/time (the same for all rows) or to evaluate per row, and I decided to use the same variable/value for all rows.

Thanks again for all of the help.

sql

How to use the multiple file connection and multiple flatfile connection?

Hi all,

I know that the File connection can be used in the File system task and Flatfile connection can be used in Flat File source or destination, but I don't know which Task or Source/Destination can use the Multiple File connection and Multiple Flatfile connection. Thanks

you have to have as many connection as the file

|||

The tasks/components that use the single version of connections should be able to use the "multi" ones if you run them in loops.

Thanks.

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.