Here is a link to a code template that should push you in a better direction, regarding structure.

That's not really the way to do it.

You can remove all of those BEGINs and ENDs (except those pertaining to TRY and CATCH).

In your CATCH block, you should probably use...

IMHO, you are on the right path.  I'm currently doing exactly what you described.  Importing flat, delimited files to a 'staging' table where all fields are defined as VARCHAR(500),...

Use DATEFROMPARTS(Year, Month, Day).

Please provide some additional background information:

1. Why did you decide not to use SSISDB environments to handle those parameters which vary between Dev/QA/Production? They make config files redundant.
2. Why have...

So restoring the ssisb did not create the master key.

No, you have to do a backup/restore of the master key as a separate task.

Is the jobs run using sql agent...

I have not heard anything about being able to do this ... but then again, I've never lived anywhere where the standard Gregorian (not English - more like Italian!) calendar...

• This reply was modified 6 years, 5 months ago by Phil Parkin. Reason: Improve narrative

Are you sure about that?

When you create SSISDB, you are presented with this screen:

After you complete this screen, the master key is created for you.

If you still...

Run the package in debug mode to determine exactly when the error is generated. Also to be 100% certain that the connection string being assembled is in the right form.

As long as it's not marked as 'sensitive'.

Try running this in SSISDB as you'll see for yourself:

SELECT * FROM [internal].[environment_variables]

Did you also recreate the Master Key? Without it, you won't get very far, as some of that db is encrypted.

Not possible, as far as I know. It's not the first time this question has been asked!

You can modify the default order in which the packages are displayed by modifying...

I presume you've considered pushing the data into a staging table first and then pivoting from that?

I would expect it to be faster than the SSIS component, and your column-naming...

Have you considered just skipping the first row?

Contact the web site admins and ask them, maybe?

Why not script out the entire thing in VB? What does your macro do?