Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storing the OLEDB destination as variable


Storing the OLEDB destination as variable

Author
Message
bucknick
bucknick
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 223
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())"
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3565 Visits: 3107
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.



twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
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
bucknick
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 223
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
bucknick
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 223
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!
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search