February 7, 2020 at 6:19 am
Hi there, everyone. I'm struggling with an SSIS package I'm working on. Here's the scenario:
I have to extract data from a SQL table to JSON format. They want it done on a level using three fields. I.e. SELECT * FROM TableA AS a JOIN TableB as b on a.field1=b.field1 and a.field2=b.field2 and a.field3=b.field3 TO JSON AUTO. ETC. I'm also using these fields later on to build the filename.
What I'm doing is I have an Execute SQL Task that gets the distinct values for a, b, c and returns it as an object FileList. This returns a full result set.
I then have a Foreach loop with Foreach ADO Enumerator on the Collection tab. It uses FileList as ADO object source variable. On the Variable Mappings tab I have VariableA, VariableB, VariableC with Index 0, 1, 2.
Inside the Foreach loop I have a script task that builds up the filename and the bcp command, which is then passed to an Execute Process Task.
It runs successfully the first time. But the second time around it fails with the Foreach container complaining that value being assigned to variable "User::VariableA" different from the current variable type.
What could the problem be?
February 7, 2020 at 2:10 pm
Run the package in debug mode so that you can inspect the variables for every trip round the loop. That should help you track down the issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2020 at 7:12 am
Thank you for your response. I finally figured out that when I create the staging and working tables one of the variables isn't created the same type across. I changed this and it's working now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply