February 3, 2017 at 5:44 pm
I have never developed a full fledged SSIS package ( been DBA for quite some time , never had to develop SSIS package for ETL process). I am working on a project where basically i need to copy data from one db to another. I want this package to be as dynamic and portable as possible. Below is what i am looking for
i) FIrst thing the package should do get connection strings from a configuration table based on a id passed something like this
DECLARE @SourceServer VARCHAR(50);
DECLARE @SourceDB VARCHAR(50);
DECLARE @TargetArchiveServer VARCHAR(50);
DECLARE @TargetArchiveDB VARCHAR(50);
Declare @archiveid int
set @archiveid =100
SELECT @SourceServer = SourceServer ,
@SourceDB = SourceDB ,
@TargetArchiveServer = TargetArchiveServer ,
@TargetArchiveDB = TargetArchiveDB
FROM Archive_SSIS_Configurations
WHERE archiveid = @archiveid ;
ii) Then it should start loading the data from the source into target table that it gets from step i. The task should be able to load data in batches.
My intent is to create a empty shell type package that will pick up all the connection strings from the config table on the fly. Any help /pointers is appreciated. TIA
February 3, 2017 at 7:59 pm
You could do this within SSIS via a script task or you could do the same thing with other tools such as PowerShell. With what you describe, you won't be using SSIS for much of anything.
Not knowing much around what you're doing and the goals you are looking to achieve, I'll make a few assumptions.
1. You have simple or no transformations and are just looking at moving data from A to B.
2. You want to write as little code as possible and/or have flexibility to make changes easily.
3. You want to implement a standard framework or pattern so that each SSIS package has the same 'look and feel'.
If any or all of the above items are true, consider the use of Biml. You'll be able to code gen the creation of your SSIS packages based on metadata. Ideally you create your logic so that your SSIS packages each has a singular focus and follows the same standard pattern.
Hope this helps.
Marc BeacomManaging Partner DatalereTAP the Power of Data(tm)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply