Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Storing the OLEDB destination as variable Expand / Collapse
Author
Message
Posted Thursday, July 3, 2014 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 3, Visits: 43
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())"
Post #1589120
Posted Thursday, July 3, 2014 11:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:48 PM
Points: 2,925, Visits: 2,526
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.



Post #1589197
Posted Friday, July 4, 2014 12:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:56 AM
Points: 632, Visits: 1,152
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 ?
Post #1589219
Posted Monday, July 7, 2014 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 3, Visits: 43
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.

Post #1589922
Posted Monday, July 7, 2014 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 3, Visits: 43
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!
Post #1589927
Posted Tuesday, July 8, 2014 12:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:56 AM
Points: 632, Visits: 1,152
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.
Post #1590195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse