October 10, 2014 at 8:01 am
Ok, So I want to create a table with history data from multiple tables example XXXXX1409,XXXXX1410,XXXXX1411.
The last four digits will change based on the (YYMM). currently the tables have from (XXXXX0501-XXXXX1410) and every week I would like to insert the data into my (loosely used term) data mart table.
I'm could load the data manually but would prefer something automated, any ideas the best way to do this. with SSIS???
Thank You in advance..
October 10, 2014 at 8:11 am
You can only use SSIS if the metadata (aka the schema) of the table stays the same.
SSIS hates changing metadata.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 8:14 am
Everything is the same.
October 10, 2014 at 8:18 am
In that case you can use a for each loop to iterate over the tables and import them to the history table using a data flow task.
Your best bet is to create a dynamic SQL statement using an expression that you store into a variable.
This variable is then used in the source component of the data flow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 8:21 am
I have used this for files, but with table names I wouldn't know what or where to start, from what I have read says table names can't be dynamic.
October 10, 2014 at 8:30 am
cbrammer1219 (10/10/2014)
I have used this for files, but with table names I wouldn't know what or where to start, from what I have read says table names can't be dynamic.
In your OLEDB Source Editor, there is an option for Data Access Mode which is 'Table Name or View Name variable' (SQL command from variable is also of interest here).
So perhaps you can now imagine looping round tables and using a such a variable to dynamically control the source table (all column info being the same, as we've already determined).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2014 at 8:40 am
Ok...This may be a strange question< but after the first initial load would I need to changed this SSIS package so it wouldn't reload all of these tables again. SO it would just load the next months data, for example the XXXXX1410 is current to today. If I am able to get this accomplished today, and loaded all this data, then next Friday would it reload all of the data, or just for the days previousof next Friday?
October 10, 2014 at 8:51 am
cbrammer1219 (10/10/2014)
Ok...This may be a strange question< but after the first initial load would I need to changed this SSIS package so it wouldn't reload all of these tables again. SO it would just load the next months data, for example the XXXXX1410 is current to today. If I am able to get this accomplished today, and loaded all this data, then next Friday would it reload all of the data, or just for the days previousof next Friday?
Not strange at all. By default, it would try to load everything again.
If you want to implement an incremental load, you have considerably more work to. You may find the relevant 'Stairway' articles of interest. Here [/url]is the first which talks about incremental loads.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2014 at 9:03 am
Will this be the same with SSIS 2010?
October 11, 2014 at 4:47 am
cbrammer1219 (10/10/2014)
Will this be the same with SSIS 2010?
This is the essentially the same in SSIS 2012 (VS 2010)
😎
October 13, 2014 at 12:57 am
Eirikur Eiriksson (10/11/2014)
cbrammer1219 (10/10/2014)
Will this be the same with SSIS 2010?This is the essentially the same in SSIS 2012 (VS 2010)
😎
And in all other existing versions of SSIS, going from SSIS 2005 to SSIS in VS2013 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 3:08 pm
Yes it would, be however the data within these table is a lot, some more than 17mb, my lead advises not to load all of this as it would cause very slow process after time, thinks should do dynamic table names, I've looked and is this even possible. I have read that it's not possible to do this. for example declare
@tablename varchar(50)
set @tablename = 'test' + SUBSTRING(CONVERT(nvarchar(4), GETDATE(), 112),3,4) + SUBSTRING(CONVERT(nvarchar(6), GETDATE(), 112),5,6)
October 13, 2014 at 3:40 pm
cbrammer1219 (10/13/2014)
Yes it would, be however the data within these table is a lot, some more than 17mb, my lead advises not to load all of this as it would cause very slow process after time, thinks should do dynamic table names, I've looked and is this even possible. I have read that it's not possible to do this. for example declare@tablename varchar(50)
set @tablename = 'test' + SUBSTRING(CONVERT(nvarchar(4), GETDATE(), 112),3,4) + SUBSTRING(CONVERT(nvarchar(6), GETDATE(), 112),5,6)
17MB? That should not really be a problem.
Unless we're back in the '80s.
You should be able to construct a SQL statement and put it in a variable (the table name is what changes each time) and use that variable in the source component.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 14, 2014 at 10:10 am
I didn't think that little of data would be that big of problem, but creating a dynamic table name is what has me stumped, from what I read it's not possible, could you provide a link or a example?
October 14, 2014 at 10:49 am
cbrammer1219 (10/14/2014)
I didn't think that little of data would be that big of problem, but creating a dynamic table name is what has me stumped, from what I read it's not possible, could you provide a link or a example?
An example:
Dynamic Database Connection using SSIS ForEach Loop Container
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply