export multiple sqlserver table s to mulitple worksheets in single excel dynamically in ssis

Question

Hi I have one doubt in ssis package Export SQL Server multiple Table into Multiple Sheets in Excel using ssis package sql server table have multiplet table like emp,location ,company . 3 tables struccture is different emp: empid |name loaction : locationid|empid|state company : companyid|depttid|empid|locationid emp table data need to load in to emp sheet. location table data need to load in to location sheet. company table data need to load in to company sheet. here I have given 3 table if any extra tables added in need to handle those sistuvations. Create an Object type variable Subject, a String type variable SheetName, and a String type variable WorkSheetQuery. In the Execute SQL Task outside the Foreach Loop Container, set its General tab as follows: ResultSet: Full result set ConnectionType: OLE DB Connection: LocalHost.TestDB (e.g. an OLE DB Connection to the source SQL Server table) SQLSourceType: Direct input SQLStatement:

 select 'create table ' +'''+ table_anme+'''+('(string_agg('''+column_anme+'''+ 'longtext' ,',')+' )' columnslist,
'select * from ' + table_name  querylist from inforamtion_schema.columns where table_name in ( 'emp','location','company')

group by table_name

1. In the Result Set tab of this Execute SQL Task, map result “0” to variable “User::Subject”.
2. In the Collection page of the Foreach Loop Container, set the Enumerator option to “Foreach ADO Enumerator”, and set the “ADO object source variable” to “User::Subject”.
3. In the Variable Mapping page of the container, map variable “User::crateable” to index “0”. User::seletquery” to index"1"
4. Set the General page of the Execute SQL Task inside the container as follows: ResultSet: None ConnectionType: excel Connection: Des (e.g. the OLE DB Connection to the destination Excel file we create above) SQLSourceType: Variable SQLStatement: User::createtable after that I am unable to load select query variable to laod corresonding data because 3 tables structure is diffeent I want donot watn use 3 dataflow each for one beacuase in the feature few more tables add in database and again package need to change. could you pleas tell me how to achive this task in ssis package

Answer 1

Hi @harinathu,

It will be quite hard to do this in simple way. If you have different columns in tables you would like to have multiple different data flow tasks. You can create the packages "dynamicly" using the BIML using for example C# - read more here: https://www.mssqltips.com/sqlservertip/3124/generate-multiple-ssis-packages-using-biml-and-metadata/ Please think about using the import/export wizard from Management Studio (later you can save this into the SSIS package and reuse).

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.