Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS DATA FLOW TASK - USING FIELD IN EXISTING TABLE

Posted on 2013-02-03
9
Medium Priority
?
334 Views
Last Modified: 2016-02-10
I have a package set up that successfully imports a csv file into a table in MS SQL 2008 database.  It imports data into all columns in that table except for 1.  In that last column, I wish to fill it in with whatever date rests in a field in another table.  The other table has only 1 record, so I thought somewhere I could say "select fieldname from tablename" and use the result for that last column.

Not sure what the best way to do this would be.
0
Comment
Question by:mak345
9 Comments
 
LVL 4

Expert Comment

by:rshq
ID: 38849296
Hi
 
  You can using UPDATE command after Import for change last field.
0
 

Author Comment

by:mak345
ID: 38849309
Isn't there a way to build a variable that selects whatever value I have in the other table and use it in the column mapping in my data flow?
0
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38849984
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Expert Comment

by:liija
ID: 38850146
Sure, map first the result set to your variable:
http://msdn.microsoft.com/en-us/library/ms141689.aspx

Then with 'Derived Column' you can add your variable as a new column inside the data flow.
0
 

Author Comment

by:mak345
ID: 38852071
I seem to be on the right track using liija's link above, but am getting an error in the Execute SQL Task:

An error occurred while assigning a value to variable "xyz".  The type of the value being assigned to variable "xyz" differes from the current variable type.  

The value I am trying to put in the variable is of "date" datatype.  The variable only lets me choose datetime.  

How do I fix?

Thanks
0
 
LVL 6

Accepted Solution

by:
liija earned 200 total points
ID: 38852191
You could cast your date column to datetime inside your SQL - since there is no 'Date' data type in SSIS.

SELECT CAST(YourDateColumn AS DATETIME) AS YourDateColumn FROM YourTable
0
 

Author Comment

by:mak345
ID: 38852228
Thanks, but I seem to be getting a different error now.  It is still occurring in the Execute SQL Task:

"Unable to find 'mydatecolumn' in the result set".
0
 

Author Comment

by:mak345
ID: 38852237
Sorry, the above actually worked.  I neglected to put the " AS yourdatecolumn" at the end.

THANK YOU!
0
 
LVL 6

Expert Comment

by:liija
ID: 38852261
You're welcome
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question