SSIS: Calling an SSIS Package From Within VB.NET Project
This is a follow-up to a previous SSIS question I had. I have a new project that requires me to take some proprietary financial metadata from one system, perform a number of fairly complex transformations on it, and create a new comma-delimited load file to be loaded manually.
I had originally looked into doing the whole project in SSIS, but the source metadata is in a very proprietary format (re: not a comma-delimited flat file), and because of this and some of the complexity of the metadata transformations I need to do, I'm thinking of a different approach.
Is there anything wrong with creating a VB.NET project that contains the logging on and extracting code, importing the metadata into a database, and then calling/running an SSIS package to transform the metadata and create a load file? I already have code written to perform the logging on, extract and importing...I could then use SSIS to read the database to map the various transformations and create the load file.
The only clean way to get the source metadata out is to use some custom API calls, and the resulting files are somewhat difficult to read. Plus, I am going to have to write some relatively complex queries against the resulting database in order to get the load file into the format I need.
Any and all advice would be greatly appreciated. I'm pretty new to SSIS, and wanted to get some honest opinions before going down the wrong road (!).
I had originally looked into doing the whole project in SSIS, but the source metadata is in a very proprietary format (re: not a comma-delimited flat file), and because of this and some of the complexity of the metadata transformations I need to do, I'm thinking of a different approach.
Is there anything wrong with creating a VB.NET project that contains the logging on and extracting code, importing the metadata into a database, and then calling/running an SSIS package to transform the metadata and create a load file? I already have code written to perform the logging on, extract and importing...I could then use SSIS to read the database to map the various transformations and create the load file.
The only clean way to get the source metadata out is to use some custom API calls, and the resulting files are somewhat difficult to read. Plus, I am going to have to write some relatively complex queries against the resulting database in order to get the load file into the format I need.
Any and all advice would be greatly appreciated. I'm pretty new to SSIS, and wanted to get some honest opinions before going down the wrong road (!).
I suggest you to use SSIS to extract, transform and load your source data tinto destination.
You can use script task inside SSIS if you need some more complex task...
Also in my blog you can see a function .net that call a SSIS Package.
Helped?
Regards,
Pedro
You can use script task inside SSIS if you need some more complex task...
Also in my blog you can see a function .net that call a SSIS Package.
Helped?
Regards,
Pedro
ASKER
PedroCGD -
Thanks for your information. I understand what you're saying, but I want to summarize what I want to do - just making sure I'm on the right track:
1. Allow the user to logon to the source system - using VB and API calls, and extract the metadata.
2. Read the complex extract files and either perform the transformations within SSIS, or import the metadata into a database (Jet?) in order to perform some of the more difficult lookups and transformations.
3. Create the load file(s).
In addition, I would like this to be compilied into a package that could be (relatively) easy to deploy and run from multiple workstations. Is that a problem wiith what I am proposing.
Thanks to all for your help.
Thanks for your information. I understand what you're saying, but I want to summarize what I want to do - just making sure I'm on the right track:
1. Allow the user to logon to the source system - using VB and API calls, and extract the metadata.
2. Read the complex extract files and either perform the transformations within SSIS, or import the metadata into a database (Jet?) in order to perform some of the more difficult lookups and transformations.
3. Create the load file(s).
In addition, I would like this to be compilied into a package that could be (relatively) easy to deploy and run from multiple workstations. Is that a problem wiith what I am proposing.
Thanks to all for your help.
You want to execute the package in each wrokstation?! Why? You need to extract and transform some data in each specific workstation?!
ASKER
Let me clarify - this is intended to be a conversion utility that will be potentially used by multiple clients. Clients will run this in order to convert their existing system to the new one.
Sorry for the confusion (!).
Sorry for the confusion (!).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/questions/24554851/SSIS-Working-with-Complex-Flat-File.html?anchorAnswerId=24855049#a24855049