April 21, 2021 at 7:40 am
Normally, we can use selected databasename--tasks--import data/export data to handle data import and data export rapidly, but this operation is required to be done manully, Can we use SSIS dynamically import or export from one table to another table? such as creating a job in backend to call it as the schedule or call the scripts(not wizard) manully. thanks!
April 21, 2021 at 10:07 am
What do you mean by "Dynamically" here? You can define the sources/destinations with expressions, yes, but the data flow part of SSIS has to be well defined so unless those objects all have the same definitions (which I doubt) then it won't be that simplistic. You'd end up needed to code your own Script Task instead; which you may well be better off doing outside of SSIS rather than in it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 21, 2021 at 10:14 am
SSIS is fairly rigid in how it works, so you have to be wary of DDL/Schema drift.
You can easily create a SSIS package which will copy ServerA TableA to ServerB TableB for example and have that on a schedule, but should one sides schema's change then it can break.
If you have regular schema drift you would want to look at something like BIML instead.
April 22, 2021 at 7:38 am
What do you mean by "Dynamically" here? You can define the sources/destinations with expressions, yes, but the data flow part of SSIS has to be well defined so unless those objects all have the same definitions (which I doubt) then it won't be that simplistic. You'd end up needed to code your own Script Task instead; which you may well be better off doing outside of SSIS rather than in it.
What do you mean by "Dynamically" here? You can define the sources/destinations with expressions, yes, but the data flow part of SSIS has to be well defined so unless those objects all have the same definitions (which I doubt) then it won't be that simplistic. You'd end up needed to code your own Script Task instead; which you may well be better off doing outside of SSIS rather than in it.
"Dynamically" It refers not to export and import with wizard, I want to know if we can achieve this funtion with script
April 22, 2021 at 7:44 am
SSIS is fairly rigid in how it works, so you have to be wary of DDL/Schema drift.
You can easily create a SSIS package which will copy ServerA TableA to ServerB TableB for example and have that on a schedule, but should one sides schema's change then it can break.
If you have regular schema drift you would want to look at something like BIML instead.
oh, thank you!
last time I created a SSIS package, it doesn't work maybe caused by the collate change
April 22, 2021 at 8:18 am
Thom A wrote:What do you mean by "Dynamically" here? You can define the sources/destinations with expressions, yes, but the data flow part of SSIS has to be well defined so unless those objects all have the same definitions (which I doubt) then it won't be that simplistic. You'd end up needed to code your own Script Task instead; which you may well be better off doing outside of SSIS rather than in it.
"Dynamically" It refers not to export and import with wizard, I want to know if we can achieve this funtion with script
You can export/import data out of SQL Server via other methods, yes, but I wouldn't call the "dynamic". For example you can import data with a BULK INSERT, or export it with bcp.
"Dynamic" tends to mean that you want to just "plug in" an object name and the system works out what you want from that; to which the short answer is no SSIS doesn't have that magic. You need to tell SSIS what it needs to do and Data Flow Tasks require explicit Data Definitions for their transformations; unless all your tables have the same definition (doubtful and if they do a likely design flaw), then you can't give SSIS those explicit definitions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply