May 23, 2013 at 9:19 am
Greetings,
I have been tasked to investigate how to develop an SSIS solution that would accept a string variable containing n-times Datasource SQL statements, and a string variable containing n-times Excel and/or Access destinations.
What they would like it to do is parse out the individual statements and destinations from the variable values, and then preform the export based on that pairing. After that completes, they want to loop back through the process and move on to the next pairing.
Example:
Variables:
@SourceSQL = 'SELECT OrganizationName, Address, City, State, Zip FROM dbo.Organizations † SELECT GroupName, GroupType, GroupDivision FROM dbo.Group † SELECT ServiceName, ServiceType, ServiceLocation FROM dbo.Services'
@Destinations = 'Orgs_Sheet † Groups_Sheet † Service_Sheet'
This needs to be either written to generate via C# code, or as a generic package that we can call from the application.
My question...is this possible? And if so how?
Thanks in advance for the help.
KJ
May 23, 2013 at 9:50 am
KJKKPSI (5/23/2013)
What they would like it to do is parse out the individual statements and destinations from the variable values, and then preform the export based on that pairing. After that completes, they want to loop back through the process and move on to the next pairing.
Example:
Variables:
@SourceSQL = 'SELECT OrganizationName, Address, City, State, Zip FROM dbo.Organizations † SELECT GroupName, GroupType, GroupDivision FROM dbo.Group † SELECT ServiceName, ServiceType, ServiceLocation FROM dbo.Services'
@Destinations = 'Orgs_Sheet † Groups_Sheet † Service_Sheet'
Where is this infromation stored? In a table in the same database?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
May 23, 2013 at 9:56 am
Abu Dina (5/23/2013)
Where is this infromation stored? In a table in the same database?
The @SourceSQL and @Destination values would be passed into a SSIS Package Parameter, via a job call. The plan to call this from a piece of C# code.
The queries being passed in would be dynamically generated from the application side by the user.
May 23, 2013 at 10:05 pm
I used a couple of examples to build a dynamic connector.
1.Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers
http://www.sanssql.com/2011/08/looping-through-sql-servers-using-ssis.html
2. Andy Leonards blog as an example. http://sqlblog.com/blogs/andy_leonard/archive/2008/08/28/ssis-design-pattern-collect-enterprise-sql-server-database-metadata-with-ssis.aspx
I used a table in repository to get server list for dynamic connections, then used @serverlist and @servername as variables. Build foreachloop which when configured right provided what I needed.
Since I was doing this to capture server and database level permissions. Instead of writing script, I used a nested loop to get a list of on-line databases. Variable @databaselist and @databasename.
Then I had to learn how to set up a variable to be evaluted as an expression to include the @databasename were I was pulling the database level permissions.
May 24, 2013 at 5:18 am
Easy way to implement this would be the following:
you create two ssis packages. One is the main package and responsible for control flow. It takes the parameters and passes them to a script task (via variables).
The script generates an ADO Dataset object containing the resulting Datatable.
(Just 2 columns of string, containing select statement and the second one destion tablename.
You cann pass the object variable to an foreach loop container which will then take the 2 string and passes them via execute child package to the second package.
The second package consists of a dataflow loading the selection to access table.
You have to be careful with metadata.
When column sets differ each query it might become very annoying.
You could probably do this more dynamically, more easily using solely .NET instead of wrapping everything up in SSIS.
If you want to be precise and prevent losing meta data you could enhance this approach.
The second package would then load any SQL Server table to a flatfile (converted to text).
Another subpackage, capable of transfering any CSV to a new (create before running package using column names and text datatype) would then fill the new table.
Data type conversion must be handled separatedly using this approach. because you'd have to convert the text columns to proper datatype.
Extracting the column definitons of the original SQL Server table and converting dynamically (.net or derived columns, data type conversion can be used too but is not as adjustable)
Good luck.
Without using staging areas or meta formats because of varying datatypes and columns this task is quite extensive.
Alternatively the datatype and column name conversion can be done on the source.
You convert the query to select all columns as nvarchar and name them after ordinal position (Col1, Col2, Col3 ... Col14).
The converted table will then be transferred to Access table and columns being renamed.
Extracting data types of columns on source table and then using this information to load all columns separatedly (1 datatype per dataflow)
might be handy too. You could then convert the types from text to specified (parameter) type inside dataflow. Joining all columns together again
requires you to take the ID with you for the joining. (Parsed source select query could be converted to always select an additional row_number column to make sure they get sorted the same way afterwards.
I'd say it depends on number of queries to process (and also number of columns).
Probably you do not need to transfer millions of records since you were talking about access destinations...
Maybe SMO is your friend here too, gotta check.
May 24, 2013 at 5:51 am
marcel.eppel (5/24/2013)
Easy way to implement this would be the following:you create two ssis packages. One is the main package and responsible for control flow. It takes the parameters and passes them to a script task (via variables).
The script generates an ADO Dataset object containing the resulting Datatable.
(Just 2 columns of string, containing select statement and the second one destion tablename.
You cann pass the object variable to an foreach loop container which will then take the 2 string and passes them via execute child package to the second package.
The second package consists of a dataflow loading the selection to access table.
You have to be careful with metadata.
When column sets differ each query it might become very annoying.
You could probably do this more dynamically, more easily using solely .NET instead of wrapping everything up in SSIS.
If you want to be precise and prevent losing meta data you could enhance this approach.
The second package would then load any SQL Server table to a flatfile (converted to text).
Another subpackage, capable of transfering any CSV to a new (create before running package using column names and text datatype) would then fill the new table.
Data type conversion must be handled separatedly using this approach. because you'd have to convert the text columns to proper datatype.
Extracting the column definitons of the original SQL Server table and converting dynamically (.net or derived columns, data type conversion can be used too but is not as adjustable)
Good luck.
Without using staging areas or meta formats because of varying datatypes and columns this task is quite extensive.
Alternatively the datatype and column name conversion can be done on the source.
You convert the query to select all columns as nvarchar and name them after ordinal position (Col1, Col2, Col3 ... Col14).
The converted table will then be transferred to Access table and columns being renamed.
Extracting data types of columns on source table and then using this information to load all columns separatedly (1 datatype per dataflow)
might be handy too. You could then convert the types from text to specified (parameter) type inside dataflow. Joining all columns together again
requires you to take the ID with you for the joining. (Parsed source select query could be converted to always select an additional row_number column to make sure they get sorted the same way afterwards.
I'd say it depends on number of queries to process (and also number of columns).
Probably you do not need to transfer millions of records since you were talking about access destinations...
Maybe SMO is your friend here too, gotta check.
Thank you Marcel. This is very helpful, and gives me something to work with.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply