Donate to support site

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 it