SSIS package to call the SP with a table name and file location and write the output to a file and run it.
Create a package DataExportControl.dtsx Tasks - dataflow task Source: Call SP s_CreateExportPackage for table SSIS_Test.dbo.ExportTest_c001c002 Destination: Write the output to a file Export_SSIS_Test.dbo.ExportTest_c001c002 Execute the created package To create the package Variables set (string) DatabaseName Name of database to connect SSIS_Test PackageLocation Location of package to create C:\test\GenericTest\Package\ SchemaName Schema of table to export dbo TableName Name of tableto export ExportTest_c001c002 FileLocation Location of file to export by created package C:\test\GenericTest\Data\ Variables set by expressions (string) TableExportPackage Location of package to create C:\test\GenericTest\Exp.SSIS_Test.dbo.ExportTest_c001c002.dtsx Expression @[User::PackageLocation] + "Exp." + @[User::DatabaseName] + "." + @[User::SchemaName] + "." + @[User::TableName] + ".dtsx" PackageBuildSP_sql SP to call to get package xml s_CreateExportPackage 'dbo','ExportTest_c001c002','C:\test\GenericTest\','.','SSIS_Test' Expression "s_CreateExportPackage '" + @[User::SchemaName] + "','" + @[User::TableName] + "','" + @[User::FileLocation] + "','" + "." + "'," + "'SSIS_Test'" Variables (Object) SPOutput This holds the output from the SP Connections ControlDatabase Database that holds the SP to provide the export package xml PackageXML Flat file connection to export the packge XML to create the export package PackageToRun File connection to run the created export package Expressions PackageToRun ConnectionString @[User::TableExportPackage] PackageXML ConnectionString @[User::TableExportPackage] To execute the package This example is using dtexec. It is run from a query as that is what I use for testing. In production I would use a command file which is generated for the environment and scheduled. exec sp_configure 'xp_cmdshell', 1 reconfigure declare @cmd varchar(8000) select @cmd = 'dtexec /f "C:\test\GenericTest\Control\ExportControl.dtsx"' select @cmd = @cmd + ' /SET \Package.Variables[User::DatabaseName];"SSIS_Test"' select @cmd = @cmd + ' /SET \Package.Variables[User::PackageLocation];"C:\\test\\GenericTest\\Package\\"' select @cmd = @cmd + ' /SET \Package.Variables[User::SchemaName];"dbo"' select @cmd = @cmd + ' /SET \Package.Variables[User::TableName];"ExportTest_c001c002"' select @cmd = @cmd + ' /SET \Package.Variables[User::FileLocation];"C:\\test\\GenericTest\\Data\\"' select @cmd exec master..xp_cmdshell @cmd exec sp_configure 'xp_cmdshell', 0 reconfigure
SSIS package to call SP, create package from output then execute ithome