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.
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.
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