October 14, 2011 at 4:02 pm
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 8:19 pm
battery_acid_h (10/14/2011)
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...
create view SSIS.G_L_Account
as
--I know, I know, "Select *" is bad.
select 'Company_Name1' NavCompanyName, * from [Company_Name1$G_L_ Account] union all
select 'Company_Name2' NavCompanyName, * from [Company_Name2$G_L_ Account] union all
select 'Company_Name3' NavCompanyName, * from [Company_Name3$G_L_ Account]
In your SSIS script task, use a parameter for the company..
select * from SSIS.G_L_Account where NavCompanyName = @id
Alternatively, you could use a table valued function instead of a view so you'd have to pass in a parameter for the NavCompanyName.
October 14, 2011 at 8:36 pm
Is there a single company table with all the company names? Can you provide us with the ddl for the critical columns for this table and some sapmle data to work with?
October 14, 2011 at 9:53 pm
If you mean business target is to generate Dynamically all the group of tables for each company , but starting with the prefix of company name like Company_Name1 , Company_Name2…etc
Then It could be done through the query below :
select * from sys.tables where name like 'Company_Name1%'
declare @companyname varchar (100)
declare Companycursor cursor for select distinct companyname from companytable
declare @sql Nvarchar (max)
open Companycursor
fetch next from Companycursor into @companyname
while(@@FETCH_STATUS=0)
begin
set @sql=N'CREATE TABLE [dbo].['''+@companyname+'''table1](
[dt] [datetime] NULL,
[val] [int] NULL,
[int] NULL
) ON [PRIMARY]'
exec sp_executesql @sql
PRINT ''+@companyname+' Table1 has been created '
fetch next from Companycursor into @companyname
end
close Companycursor
deallocate Companycursor
However , you have to identify all Create DDL statements for all other tables like what done above for table1 as for example:
N'CREATE TABLE [dbo].['''+@companyname+'''table1](
[dt] [datetime] NULL,
[val] [int] NULL,
[int] NULL
) ON [PRIMARY]
And pass those statements to @sql to be executed within each cursor loop
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 14, 2011 at 10:46 pm
This one is actually what I want to avoid, repeating the same script for each of the 50 odd companies. By that I mean, the union all script, which is what I am doing now, the issue is also, by hardcoding into a view, and they add a company, then the data will not come across.
October 14, 2011 at 10:57 pm
Thanks for all the replies!
Let me explains some more: The 50 companies each has a number of tables I need for a data warehouse and cube development.
The company table contains the names of all the companies ( I am not going to list all of the names, lets go with 5)
Say
"Wester Texas Roofing"
"World Class Flooring"
"South Florida Roofs"
"Dakota Roof builders"
"New York Roofs" ( but there are 50 ...)
Each of theses companies has a number of tables specific to the datawarehouse. Instead of doing a source and destination data extract, with scripts where I specifically repeat the same script 50 times, I thought I could loop over the tables like this
select ..... from @companies$Project]
then
select .... from @companies$Project Ledger Entry] each with a Foreachloop until all the tables are done and datawarehouse populated every night.
Also, some tables are say [Western Texas Roofing$Project Ledger Entry] and conatins spaces in the names, so the [] has to be included somehow.
I tried to do select '['+Name from dbo.Company to achieve that, but still no go.
However, sql doesnt allow me to use variables as table name prefixes. I have tried several ways of doing it, but no luck.
October 14, 2011 at 11:24 pm
If not to repeat tables, you could run the query below and schedule it also to seek any new company added and create its appropriate table group
declare @companyname varchar (100)
declare Companycursor cursor for select distinct companyname from companytable
declare @sql Nvarchar (max)
open Companycursor
fetch next from Companycursor into @companyname
while(@@FETCH_STATUS=0)
begin
set @sql=N'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id <> OBJECT_ID(''[dbo].[@companyname$table1]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].['''+@companyname+'''$table1](
[dt] [datetime] NULL,
[val] [int] NULL,
[int] NULL
) ON [PRIMARY]
PRINT '' '+@companyname+'$Table has been created ''
END
ELSE
PRINT ''Table is already exists''
'
exec sp_executesql @sql
fetch next from Companycursor into @companyname
end
close Companycursor
deallocate Companycursor
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 14, 2011 at 11:37 pm
Might I ask you ,have you here a composite task of :
Seek any new company added and then create its appropriate table group..?
Then you need to proceed a datawarshouing for them accroding to table type ...like :
Companyname1$_profit with
Companyname2$_profit
Companyname3$_profit ...etc
Please clarify
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 14, 2011 at 11:46 pm
Hi
No, the data from the tables of all the companies go to a single table in the datawarehouse
so,
All Project Ledger Entries from the 50 different tables in OLTP database go to a single tables called fProjectLedgerEntries.
No table creation is needed, its extraction of the data from the different tables which is the problem. I just thought I could avoid repeating the query for each one of the tables - currently if I want Project Ledger Entries - I need to repeat the same script 50 times to get the total project ledger entries,, but if I could loop over something like this:
select * from @Company$Project Ledger Entry] and the @company is an object variable that is created upstream in the SSIS task.
October 14, 2011 at 11:59 pm
Oh yes, I cannot used a stored procedure.. the customer's source database is read only, no object creation
October 16, 2011 at 11:41 am
battery_acid_h (10/14/2011)
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...
I'm not sure if this was asked or not, but what type of error message(s) are you getting.
October 16, 2011 at 11:43 am
Also, do you have a separate task accessing each table? I would think that each specific table has the same layout so that issue is simply changing the source connection to access each table for each company.
October 16, 2011 at 5:28 pm
battery_acid_h (10/14/2011)
This one is actually what I want to avoid, repeating the same script for each of the 50 odd companies. By that I mean, the union all script, which is what I am doing now, the issue is also, by hardcoding into a view, and they add a company, then the data will not come across.
Gosh... dynamically rebuilding a common view on demand from a table of company names isn't difficult to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply