I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.
EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".
Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.
The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">
<ConfiguredValue></ConfiguredValue>
</Configuration>
</DTSConfiguration>
The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>
Then save the package.
When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.
Projects now have two flavours based on their deployment model: package based (classic) or project based (new). Package is available from 2005 - 2012. During development, you develop solutions that solve business problems. Problem is, once you're outside of Visual Studio/BIDS/SSDT, the wholistic concept goes away. Project model creates an .ispac file, which is more akin to an C library-3/4 of an assembly is no good, you must have it all. Those .ispac files upload to special SSISDB catalog which is new in 2012
The SSISDB is a dedicated catalog for hosting Project deployment model projects in SQL Server. Organization is Folders that you create. A folder can have 2 item types in it: Projects and Environments. A project is an SSIS project. There can be many in a single folder or just one. An Environment is the 2012 approach to configuration/parameterization of packages. Environments can be many or just one per folder. There is no automatic linkage between an Environment and a project's parameters or connection managers. An environment may be used across projects in multiple folders.
Projects in folders in the Integration Services Catalog can have manual, hard coded configuration applied to them or you can use an Environment to supply those configurations. The difference can be subtle. Assume 2 different SSIS projects that reference the Sales database. Same resource. You could either use the Configure option and manually define the server per environment (dev, test, prod) per project, or you create 1 Environment that defines the value for the Sales server. Then tell each project to re-use the same resource for configuration. That server goes belly up, one change fixes all.
From SSISDB folders, projects, environments explanation
Integration Services Catalog
Getting the nomenclature sorted out would have probably helped me get this stuff straight.
1. SSISDB
The SSISDB is the catalog where all the 2012+ SSIS packages will be deployed, if you're using the new Project deployment model. It will always be named SSISDB for SQL Server 2012.
Right click options
- Active Operations - Lists all currently running SSIS packages with an option to Stop the selected package.
- Create Folder - Creates a folder with an optional description
- Start PowerShell - Self explanatory
- Reports - A variety of native reports about package executions.
- Delete - This deletes the SSISDB catalog. Doing this will result in having to recreate the catalog and a loss of all your history.
- Refresh - Self explanatory
- Properties - Contains information about the default logging settings and the project versioning.
2. Folder
A folder contains Project(s) and, optionally, Environment(s).You must create a folder. The deployment wizard will not automagically create one for you. You may call it whatever you like. You may have all of your SSIS projects under a single folder, have a 1:1 mapping between projects and folders or anything in between. Folders are only 1 level deep - No nesting
Right click options
- Start PowerShell - Self explanatory
- Reports - All Executions, All Validations
- Delete - This deletes the folder, assuming there are no Projects or Environments below it.
- Refresh - Self explanatory
- Properties - Contains the name, version and a description.
3. Projects
This is automatically created when a folder is created.
Right click options
- Deploy Project - IS Dpeloyment Wizard
- Import Packages - Integration Services Project Conversion Wizard
- Start PowerShell - Self explanatory
- Reports - Only Custom Reports are available
- Refresh - Self explanatory
I just want to build a simple test using SSIS, import a file into a table, which of the above are required, and in what order do I create them?
You would need only to create a single package to handle moving data from a file into a table. You can execute it from Visual Studio which requires none of the above "stuff."
However, in a SOX/SAS 70 world situation, you are probably going to have insufficient permissions to perform the load in a production environment (separation of duties). At that point, you'd need to plan for how the packages will be deployed, how you will modify environment specific values (Sales server is on sqldev/sqltest/sqlprod), how the packages will get launched (scheduled vs event drive vs at will), etc. That's when you'd probably want to deploy to the 2012 Integration Services catalog. It isn't a requirement though, you can run project deployment model packages from a .ispac, but it's not as convenient an option.
The order you create them is
1. Create a project in Visual Studio/SQL Server Data Tools (BI)
1. Create a folder in Integration Services catalog
2. Deploy a project
3. Create an Environment (optional)
4. Configure the project (either link to an Environment or manually set the values)
5. Run the package in SSMS by right clicking run, click the checkbox to ensure configuration values have been applied
Also, do you have any good links to basic SSIS 2012 ETL overviews and/or how to articles
My go to writeup on getting SSIS under your belt is Andy Leonard's series on SSC, Stairway to Integration Services. He's a good author and he looks good too (as he would put it). Focus on learning the essentials of SSIS itself and then you should be able to understand the finery of the 2012 deployment or at least figure out specifically what questions you have.
Best Answer
Progress' collation tables are to control how indexes are sorted and assigned and some character handling if you are operating across several locales. Open Edge comes with a variety of collation tables for popular languages. The purpose is to help with sorting and comparisons. You can specify collation tables upon startup. If you do not but name a database in startup Progress will use the collation table for that database. If you do not specify either then Progress will use a collation table called BASIC.
As for MS SQL: both databases have data in the same language, correct? And your ODBC test was from SQLServer -> Open Edge which was successful. Some things to watch out for:
In case you are interested, Open Edge does have a SQLServer specific DataDirect branded ODBC driver you can use. It is intended to make connections to SQLServer easier.
Make sure you have the Open Edge driver on the MS ODBC Data Source Administrator box you will use.
I've heard of issues with the 64 bit versions of various drivers and the ODBC Data Source Administrator. I do not know if they are now fixed in late 2017, but if you have the 32-bit versions they may be more compatible all around.
How To options:
You can use SSIS. Build your package and run it to import the data into SQL Server. If you want to try any automation make sure that the SQL Server Agent job runs on the same server as your ODBC Data Source Administrator and the user has the privileges to all of those areas.
You can use the SQL Server Import/Export wizard to move your data. This might be the easiest way. Setup your data source and target and import data from Open Edge. They do note that any binary data will be converted to "datatype 23", and you have to manually go to each column definition and set the datatype in the dropdown to binary. To play it safe, check the datatypes after you import.
Another option, if you wanted to go this way, is to use free ETL tools to move your data. You don't say how much, how complex or how often; but ETL is another choice. This would remove the connection concerns since these packages typically come with a variety of connectors for many types of data sources. If that interests you, you can check out this ETL wiki for options.