How to assign an expression with a ssis variable?

Hi all of you,

That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.

Something like that:

'La tabla "' + SUBSTRING( @.[System:ackageName], 3,20) + "' se ha cargado correctamente'

TIA for that,

Sorry, it's solved

" La tabla " + SUBSTRING( @.[System:ackageName], 3,20) + " se ha cargado correctamente "

How to append only new records

What would be the steps to create a SSIS job to append records from an ODBC table to a SQL table, adding only the records from the source that do not already exist in the destination?

I have another post on this subject, with a good suggestion for the approach, but I need some more detailed instructions for implementing it:

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

All details here:

Checking if a row exists and if it does, has it changed
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

(and in the various links from it)

-Jamie

|||

This is excellent. Just what I was looking for, and a little bit more...

Many thanks

|||

Jamie's answer did the trick

All details here:

Checking if a row exists and if it does, has it changed
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

(and in the various links from it)

-Jamie

Edit: Can't mark this issue solved...

How to append only new records

What would be the steps to create a SSIS job to append records from an ODBC table to a SQL table, adding only the records from the source that do not already exist in the destination?

I have another post on this subject, with a good suggestion for the approach, but I need some more detailed instructions for implementing it:

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

All details here:

Checking if a row exists and if it does, has it changed
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

(and in the various links from it)

-Jamie

|||

This is excellent. Just what I was looking for, and a little bit more...

Many thanks

|||

Jamie's answer did the trick

All details here:

Checking if a row exists and if it does, has it changed
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

(and in the various links from it)

-Jamie

Edit: Can't mark this issue solved...

How to add reference to SSIS script task

Hi ALL

I cannot add the reference to the following:
"Imports Microsoft.AnalysisServices.AdomdClient"
in SSIS script task. All the objects that i declared become underlined because there is no reference to the above library.
When i go in SSIS script task and open the code to add the reference it does not the list the above library.
I am able to add reference in Visualstudio but not in SSIS.

Please adivse, it is very critical and urgent for my task

Thanks.

Does this help?

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Ed

|||

As Ed says, you will need to copy the DLL. Note that you only need to do this at design-time so you won't need to do it on your production server.

-Jamie

|||

Hi Jamie / Ed

Thanks for the reply, it did work for me. Appreciate your help.

Thanks

How to add existing dtsx file back into VS project

I recently did a save as for my ssis project in VS. Now when I open my sln file, it doesn't show the previous package name, it shows the new name. This is not what I want. I thought that doing a save as would separate the new package name into it's own project.

I removed that package name from my solution explorer and tried to add back in the old dtsx filename under SSIS packages in Object Explorer. I did a right click on SSIS Packages and choose "add existing package", chose the old dtsx file. It created a whole new filename with (1).dtsx appended to it.

How can I get this project back where it used to be? I'm using VS 2005

It sounds like your package may be in the source control system already when you try to re-add it. Have you tried to right-click on the project and selecting Get Latest Version? That will ensure that you're in synch with VSS and then you could do you rename after you have that release.

-- Brian Knight

|||

When you do save as, it also changes the name in the solution, yes. The old package is still there, just right click on the project node and select Add Existing Package like you said, but then just rename the package when you load it.

K

How to achieve partition parallelism?

Hi,

Is it possible to achieve partition parallelism in SSIS? What I am asking is, In DataStage, if I load some data like 'data reader -> trans1 -> trans2 -> destination' (and assume that I have 4 nodes configured), the tool divides the data into 4 different datasets and executes the package as 4 instances. This way the data load is very fast. Is it possible in SSIS?

Of course we can divide the dataset and load them thru multiple instances? But then dividing the dataset will differ for every load and so we need to modify the package all the time. Even if we divide the dataset, I am not sure 4 instances will run in 4 different nodes or in a same node? So anybody has any idea about it?

Thanks.

You can't do that like you can in Data Stage/Ab Initio etc... unfortunately, no. The only way is, like you describe, mimicing parallellism by breaking teh data up explicitly and executing (say) 4 different data-flows.

I wouldn't be surprised to see it in an upcoming version though. I know its on their radar.

-Jamie

|||Thanks for your clarification Jamie. If I mimic parallelism, how can I ensure that each instance is executed in different node? Is it possible to do it?|||

Have a look at this entry in BOL: http://msdn2.microsoft.com/en-us/ms345184.aspx

One approach that can be successful is to use a conditional split to divide a data load to (say) four remote raw files, then use SQL Server Agent to execute jobs on the four remote servers to read the raw files.

The performance is impacted by the need to write and read the data from the raw files on disk, which may negate any benefit of the parallelism. However, net benefits depend on the size of the load, nature of the data, speed of the network, and speed of read/write from disk.

Donald

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to accomplish this?

I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.

I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:

Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed

In the end I want to create two tables like so:

Table 1:
Event A / Closed
Event B / Modified

(Event C is not here because it was voided, so any prior records of C are gone).

Table 2:
Event A / Property A / Closed
Event A / Property B / Created

(Only events with properties would be in this table).

In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.

Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.

"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).

I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).

I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.

So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?

Thanks for your help.May be you need to move the 'modifies' logic to a separate dataflow after the 'creates' are done. I don't think there is a easy way of enforcing the order of parallel data pipelines in a single dataflow.|||

Hi StGeorge,

I think you need to create some mechanism by which to order properties. I trust you when you say your actual process is more complicated, so I may be headed down the path with this suggestion - but here goes:

It sounds as if you are adding a row to Table 2 on a state named Created. It also sounds as if you are removing this row from Table 2 and its parent row in Table 1 on a similar state named Void.

Would it work if you created an integer column in Table 2 called PropertyOrder? You could populate it with 1 on Created, 3 on Void, and 2 for any other property event. In SSIS, you could then load the records from the source ordered by this field. If your transactional environment is intense (lots of asynchronous transactions), you may be forced to stage the ordered data (in a table or raw file) and then act on this resultset in a subsequent data flow or Execute SQL Task to guarantee transactional integrity (which is how I interpret Table 1).

I may be way off here. If I am, please accept my apologies.

The method I propose above is roughly analogous to a method many deterministic network protocols use to guarantee packets are output in the order sent. The packets are generated synchronously, transmitted asynchronously, received in any order, cached, and reconstructed by ordered key before delivery. I think you have all the pieces except the key by which to order the records.

Hope this helps,
Andy