October 14, 2011 at 4:06 pm
I posted this by mistake in TSQL forum...
If anyone is familiar with Dynamics NAV and the database structure, or even if this is something familiar, please help!
The dynamics NAV databases can have several companies in a single database. each company has its own set of tables, identified with a tables prefix. This means that each company has a full set of the " same" tables.
example:
[Company_Name1$G_L_ Account]
[Company_Name2$G_L_ Account]
[Company_Name3$G_L_ Account]
To do SSIS packages for these tables, become a nightmare when there are a huge number of companies. I have tried using a variable for the table prefix (the bit before the $), in a foreachloop container but its not successful.
Has anyone been successful bedding a SQL script into a SSIS script task within a foreachloop, so that it will repeat for each company it finds in the company table? I cant code and would really appreciate the assistamce...
October 14, 2011 at 4:17 pm
Never seen that done, but I offer a different path.
It sounds like you want to walk through a set of tables based on their prefix, you could use a regular LIKE query in a EXEC SQL Task to fill an object in SSIS by setting the resultset to be Full Resultset and the variable that it is written into is of type object.
You then use the ADO row set enumerator and you get your table list that way..
Clear?
CEWII
October 14, 2011 at 4:27 pm
I think I know what you mean, but could you give me an example, so I am really clear, please?
October 14, 2011 at 4:40 pm
the "LIKE" part is where I struggle. how do i do select from "like"? if I select from sysobjects, I only get a list of the tables where the name is "like"
October 17, 2011 at 12:24 pm
SELECT name FROM sys.tables WHERE name LIKE 'SomePatternAtTheStartOfTheTableName%'
Will give you all tables that start with "SomePatternAtTheStartOfTheTableName"
Clear? Sorry it took a while to get back I've been EXTREMELY busy..
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply