Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Manually changing flat file source has no effect

I have a data flow that reads from a flat file source, goes through one data transformation component to change from unicode to normal text and writes the data to a SQL Server table. This has been working fine throughout development using a specific source file as input. I have now manually changed the path and name of the input source file in the connection manager to point to a new file and the task continues to process the old text file.

I open the connection manager and check its properties and preview the data and it all looks fine - it is finding the new file. I also edit the flat file source component and preview the data and it shows the data from the new file. I run the data flow by right-clicking and selecting Execute Container and it continually reads the old file and processes it! (I do the right-click thing because this is just one small part of a larger package.)

This has got to be a bug, but just where I wonder. Anyone ever see this before? I'm going to try to run the entire package in debug mode, instead of right-clicking, next and see if that's any different. Anyone have any ideas on how to force a refresh of the necessary internal components to make it read the new file? All the external properties point to the new file, but it's not being read.

Joe

Update:

I ran the entire package and found no difference in execution - it's still reading the wrong input file. I then deleted and recreated the connection manager, again specifying the new file to read. It still continued to read the old file. I deleted the flat file source component and recreated it, specifying the latest connection manager (twice, since I must have pointed it at the wrong one the first time and it read a completely different file). I still have the same problem of it reading the wrong input file. I don't know what else to recreate that would have any effect. Does anyone have any ideas? I need to change the pointer to different files multiple times and have it read several different input files. This has got to work somehow. Any help is appreciated.

Joe

Perhaps you have a Package Configuration enabled that you have forgotten about? Have you ever run the package configuration wizard on this package? Do you see anything in the log or progress report to suggest that a config is being loaded? What do you see in the progress window?

When you deleted and recreated the connection manager, did you create a new name for the connection manager, and then change the source adapter to use the new connection manager? That would throw off any configurations.

Finally, you could try renaming the original source file - the package may fail, but the specific point of failure may tell you more.

Donald

|||

Thanks for your comments, Donald. I ran it again this morning and got the same error, but, as suggested, I checked the Output log and did indeed see a reference to a configuration file. I would have said that I had no configuration set up for this package, since I only started looking into configurations a couple of weeks ago. However, I was out of the office for a week and I must have gotten further than I thought, because there was a configuration set up for this package. My memory is already starting to deteriate.

Sorry for taking your time, but I sure do appreciate your help.

Joe