Storing the OLEDB destination as variable

Question

Storing the OLEDB destination as variable

bucknick

SSC Enthusiast

Points: 125
More actions
July 3, 2014 at 1:39 pm

#296272

I am working on a project where I will be loading 50-100 tables from an Oracle source to SQL. I want to write the Destination tablename, starttime and endtime to a table so users can see what the status of the loads are. I added event handler that does this but I am not finding a way to dynamically insert the table name of the destination. My first thought was to just to name the package the same as the destination table but I'm not finding a system variable that will work. Any suggestions?
This works but the table name is static:
"INSERT INTO Table_load_log(TableName,StartDate,EndDate) VALUES ('Table1','" + (DT_WSTR, 10)(DT_DBDATE)@[System::ContainerStartTime] + " "+ (DT_WSTR, 8) (DT_DBTIME)@[System::ContainerStartTime]+ "',GETDATE())"

Viewing 6 posts - 1 through 5 (of 5 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Depending on how you name your packages and tasks, system variables PackageName and TaskName might help you. Otherwise, you could try storing some addition information external to your package with the PackageID or TaskID as the key.

You could also consider using the out of the box logging. This can log the start and end time of relevant tasks for you (i.e. no code to write to get information logged. This can be used to log the package name with its start/end time, task start/end time. All you need to do is to write reports against the log table.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

bucknick (7/3/2014)
I am working on a project where I will be loading 50-100 tables from an Oracle source to SQL.

when you say 100 tables how exactly have you running your package, loading is done individually like data flow task (hardcoded destination table) or you are getting it from anyother source ?

because if you are getting these from a table/other source, then you can use FOR Each loop container, in that container you can get the name of the table in to a user variable and rest come be done the same as you have written. Make sense ?

bucknick SSC Enthusiast Points: 125 More actions · Answer 3

I changed the name of the task name to represent the table and that would be a work around but would probably want something more dynamic. For some reason, when I use package, it just says "package" when the expression evaluates. I'm not sure if this is because I am saving the package to a file system. The package name would be a better route for me because in the future I plan on using BIML and will build packages (which will be the table name) using a meta data table.

bucknick SSC Enthusiast Points: 125 More actions · Answer 4

I am using a hardcoded table.

I've seen some people use this approach but I don't have much experience with FOR loop. Do you know of any documentation that would help with this?

Thanks!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

there are a lot of FOREACH Loop for SSIS :

This one will give you a head start.

http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

hope it helps.