August 13, 2017 at 10:40 pm
Should a package level variable that is:
1) Created in a Script Task, inside a Sequence Container,
2) Then passed to the Control Flow Task,
3) Be available in the Data Flow Task???
It's easier to illustrate the issue with screenshots, so please have a look at the attached Word doc. I've also attached a PDF document if you're nervous about downloading a Word document, or if that's just easier for you.
Thanks...
August 14, 2017 at 5:36 am
Yes, the variable's value should be available to all tasks in the package, given that the variable is scoped at package level. What makes you think that it is not? Have you tried adding breakpoints to confirm your suggestion?
What I cannot see from the document (I may have missed it), is the value contained in SqlCopy.
It would also be useful to see the exact text of the error message.
What may be happening here is a design-time validation error, but I'll need more info to be sure.
It also looks that you may be trying to do a dynamic data flow (where the number and names of the columns are not known in advance). This cannot be done out of the box.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 15, 2017 at 7:51 pm
Hi Phil,
Thanks for your reply.
I've uploaded a test SSIS solution to https://1drv.ms/f/s!AtQzpRq7KAUEgdVJfZEdxIadQekW-w with public access (expires in 30 days - 15Sep). I've developed it in SSDT 2015 - hopefully you can read it.
If you choose to download it, copy the scripts to C:\Temp\Scripts, or change the project ScriptsPath parameter. You'll need to update the Project level MyTestConnMgr (of course).
There are 5 (small) packages, Test1-Test5, each building on the one before it.
It "works", sort of, but here are my expectations:
Test1: Uses Script Task + C# to read the three files into the variables SqlCopy, SqlCreate, and SqlPostProcess. After Test1 executes, I expected the current values of these variables to be reflected in the Variables window. Instead they remain blank. However, they only appear blank in the Variables window, but must have the correct values otherwise the following steps wouldn't work.
Test2: Creates the target table via the User::SqlCreate variable. So even though I can't see what is executing, it worked. So far, so good.
Test3: Loads the target table. For this package, the source is an embedded SQL command (created by browsing to C:\Temp\Scripts\Copy_Test1.sql). Preview works as expected. Columns display as expected. For this package, the target is an explicit table name [dbo].[Test1]. So far, so good.
Test4: Builds PK + index via the User:SqlPostProcess variable. Again, so far, so good.
Test5: This is the package that gives me the most "grief". In the Data Flow task, I've changed the source table to SQL command from variable, variable name User::SqlCopy. The variable value does not display, and appears blank. Preview doesn't work. Columns don't work. I've changed the target table to Table name or view name variable - fast load, variable name User::TableName. View existing data does work, since this variable is explicitly defined via an expression. Mappings work, but I'm not sure if this is "cached" from when the source table was explicitly defined? However, again, it works, so far, so good.
In summary, if I use a Script Task to return a package variable, I would expect that variable value to be displayed everywhere "appropriate" (after executing the Script Task that loads the variable), and the project functionality to be identical to if I pasted in that variable value instead of referencing the variable name.
In my "real project", I've resorted to explicitly defining the SQL command for the source table via the Browse button, due to the issues above. My preference would be to read the value via the Script Task.
Edit: In Test5, the property ValidateExternalMetadata=False for the source and target tables in the DFT.
August 16, 2017 at 6:13 am
Hello Scott
I won't download the solution just yet, because I believe I understand the issue you are having.
As I cryptically implied in my earlier message, configuration of data flow columns is a design-time exercise. You cannot do this dynamically at run time.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply