Help with data flow

I would like to use Integration Services to update data in my datawarehouse. I have a table called "AgentStats" that stores archived data from the past 3 years. I would like to import the current year's data from the production server into the same table in my datawarehouse and have my ETL update only the current year's day on a daily basis. The current year's data is constantly updated in the datasource, so I achive the data at the end of the year. Any ideas how I can accomplish this?

Thank You

-Sam

This seems like a simple task, so here goes-

Source (Filter on date to restrict to current year if more than that present) --> Lookup (Lookup to detect if current row exists, using PK columns) --1-> Insert when not exists, Destination

--2-> Update when exists, OLE-DB Command

Things are often more complicated than this, and there are several ways of doing the same job, but that outlines the simplest.

|||Read through this thread: Checking to see if a record exists if so update else insert

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".

Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman