February 3, 2014 at 1:07 pm
New to SSIS have done mostly Import Export wizard jobs with small modifications.
I have a Flat File Source that has 30 columns and a Destination Table that has 31 Columns. The 31st column of the Destination Table is set NOT NULL
I want to Pull the Value of 1 row and 1 column from a different table in same DB as Destination Table but I need to add a Query to select the right row by Selecting MAX(date) based on datetime the Job is running.
Example I have a Column that represents PayPeriod of W05 (for week 5 of 2014)
this has a PayDate that is 2014-01-31 I want to Query this table for MAX(PayDate) which would give me the right row then tell my query to select PayPeriod that matches this row and insert the same W05 in Column 31 of all the rows it would insert into the destination table based on the Flat File source. So if there were 981 rows in the flat file it would insert W05 for each of those 981 rows along with all the rest of the data from the flat file. I need this to be Dynamic so if I run this every PayPeriod it would select the Next MAX(PayDate) and know that this time it is W06
Any help would be appreciated.
Thanks,
February 3, 2014 at 1:26 pm
I found some information. I believe I need to add a lookup and use that lookup to run the following Query:
SELECT PayPeriod
FROM dbo.LS_PAY_PERIOD
WHERE PayDate IN (
SELECT MAX(PayDate)
FROM dbo.LS_PAY_PERIOD
)
Where I am still lost is how do I tell it to insert into each new row with Flat file data.
February 3, 2014 at 5:38 pm
May be you can query the separate column and store it in a variable. Add that variable in the Derived column as 31st column.
Vikash Kumar Singh || www.singhvikash.in
February 3, 2014 at 11:51 pm
jameslauf (2/3/2014)
New to SSIS have done mostly Import Export wizard jobs with small modifications.I have a Flat File Source that has 30 columns and a Destination Table that has 31 Columns. The 31st column of the Destination Table is set NOT NULL
I want to Pull the Value of 1 row and 1 column from a different table in same DB as Destination Table but I need to add a Query to select the right row by Selecting MAX(date) based on datetime the Job is running.
Example I have a Column that represents PayPeriod of W05 (for week 5 of 2014)
this has a PayDate that is 2014-01-31 I want to Query this table for MAX(PayDate) which would give me the right row then tell my query to select PayPeriod that matches this row and insert the same W05 in Column 31 of all the rows it would insert into the destination table based on the Flat File source. So if there were 981 rows in the flat file it would insert W05 for each of those 981 rows along with all the rest of the data from the flat file. I need this to be Dynamic so if I run this every PayPeriod it would select the Next MAX(PayDate) and know that this time it is W06
Any help would be appreciated.
Thanks,
- Create a package-scoped string variable to hold the pay period.
- Use an ExecuteSQL task to query the db and assign the returned pay period to the variable you created above.
- Then, in your data flow, add a derived column. Set the value of the derived column to the variable you created.
- Map the new column to the target as required.
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 4, 2014 at 9:02 am
Thank You I will have to try this using a variable ( I am a little lost on how to do that so I will have to do some investigation) In the mean time what I did was take the Flat file and sourced it into a Derived column and made the 31st column. I then pointed it to a lookup and matched my query to the derived column then mapped all correct columns in destination. I know this adds an extra step but I will check into making a variable now.
Thanks again for the help.
February 4, 2014 at 12:48 pm
Hello I need a little more help with the SQL task to Variable.
I have made a SQL Task and set the result to Single Row and put in my Query inside the query builder.
In the result set screen of sql task I added the name on my Variable.
Now when I try to run the task it fails with this message.
"failed with the following error: "Parameter name is unrecognized." Possible failure reasons: Problems with the query, "ResultSet" property not set correctly
The result of my query will produce a 3 Character String so it has to be set to Single row. I am not sure where I am going wrong here.
February 4, 2014 at 1:59 pm
jameslauf (2/4/2014)
Hello I need a little more help with the SQL task to Variable.I have made a SQL Task and set the result to Single Row and put in my Query inside the query builder.
In the result set screen of sql task I added the name on my Variable.
Now when I try to run the task it fails with this message.
"failed with the following error: "Parameter name is unrecognized." Possible failure reasons: Problems with the query, "ResultSet" property not set correctly
The result of my query will produce a 3 Character String so it has to be set to Single row. I am not sure where I am going wrong here.
Have a look here[/url] and compare with what you have done.
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 4, 2014 at 3:34 pm
Thanks. There was something wrong with the SQL task so I deleted it and created another and it works.
When I say it works i mean I am able to execute that task without error.
I have one more (hopefully last) error.
when I pass the variable to my derived column I am using the expression @[User::MyVariable] with a DataType of string [DT_STR] Length of 3. I am getting a truncation Error and it says:
[Derived Column 1 [1278]] Error: The "component "Derived Column 1" (1278)" failed because truncation occurred, and the truncation row disposition on "output column "Pay Period" (1281)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column 1" (1278) failed with error code 0xC020902A while processing input "Derived Column Input" (1279). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
the data I am pulling is a 3 character string both the database I am pulling from and inserting into have this column as Char(3) Not Null any Ideas what is causing truncation?
February 5, 2014 at 12:09 am
Can you post the T-SQL of the query from your ExecuteSQL task please?
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply