[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

Basic Package

I'm just learning to work with SSIS and could really use a lot of help! I need to convert an existing stored proceedure to a SSIS solution. I'm not sure where to even start since I'm dealing with a loop and a cursor. Should I begin with a Execute SQL Task? If someone could walk me through this it would make life beautiful, but any help would be really appreciated. Here is the stored proceedure:
****************************************************************************8
USE [MyDatabase]
GO

/****** Object:  StoredProcedure [dbo].[Sync_Lab_Test_PF] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sync_Lab_Test_PF] AS
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
      declare @myLasttime as binary(8)
      declare @RecCount as int

      declare @myRevTimestamp as binary(8)
      declare @TempTimestamp as binary(8)

      declare @Customer_No as varchar(20)
      declare @TC_No as varchar(20)
      declare @Draw_Date as Datetime

      declare @OutsideLab as tinyint
      declare @Recommendation_Code_ID as varchar(20)
      declare @Comment_Code_ID as varchar(10)

      declare @PowerFactor100 as decimal(20,5)
      declare @PowerFactor100Result as int
      declare @PowerFactor25 as decimal(20,5)
      declare @PowerFactor25Result as int


      -- only one record in the NavSync database

      set @RecCount = 0

      select @myLasttime = myTimestamp from NavSync where TableID=9

      if @@rowcount=0
      begin
            Insert NavSync values(9,1,getdate(),getdate(),0,'PFPosted')
            select @myLasttime = myTimestamp from NavSync where TableID=9
      end

      DECLARE syncLT_PF_cursor CURSOR READ_ONLY FOR  
            select [timestamp],
             [Customer No_],
             [TC No_],
            [Draw Date],
            [Outside Lab],
            [100 Degree Power Factor],
            [100 Degree Result],
            [25 Degree Power Factor],
            [25 Degree Result]

      from MYSQL.MyDatabase.dbo.[TableName]
      where [timestamp] > @myLasttime
--      and [Modified Date] > '04/27/2006'
      order by timestamp

      OPEN syncLT_PF_cursor
      
      FETCH NEXT FROM syncLT_PF_cursor INTO
            @TempTimestamp,
            @Customer_No,
            @TC_No,
            @Draw_Date,
            @OutsideLab,
            @PowerFactor100,
            @PowerFactor100Result,
            @PowerFactor25,
            @PowerFactor25Result


      WHILE @@FETCH_STATUS = 0 and @RecCount < 40000
      BEGIN
      
            update PowerFactorPosted set
                  OutsideLab = @OutsideLab,
                  PowerFactor100 = @PowerFactor100,
                  PowerFactor100Result = @PowerFactor100Result,
                  PowerFactor25 = @PowerFactor25,
                  PowerFactor25Result = @PowerFactor25Result
             where  Customer_No = @Customer_No and TC_No = @TC_No and Draw_Date = @Draw_Date

            if @@rowcount = 0
            begin
                  Insert into PowerFactorPosted
                  (
                        TC_No,Customer_No,Draw_Date,
                        OutsideLab,
                        PowerFactor100,PowerFactor100Result,
                        PowerFactor25,PowerFactor25Result
                  )
                   values
                  (
                        @TC_No,@Customer_No,@Draw_Date,
                        @OutsideLab,
                        @PowerFactor100,@PowerFactor100Result,
                        @PowerFactor25,@PowerFactor25Result
                  )
            end

            set @RecCount = @RecCount + 1
            set @myRevTimestamp = @TempTimestamp

            FETCH NEXT FROM syncLT_PF_cursor INTO
                  @TempTimestamp,
                  @Customer_No,
                  @TC_No,
                  @Draw_Date,
                  @OutsideLab,
                  @PowerFactor100,
                  @PowerFactor100Result,
                  @PowerFactor25,
                  @PowerFactor25Result
      END

      CLOSE syncLT_PF_cursor
      DEALLOCATE syncLT_PF_cursor

      if @RecCount > 0
            begin
                  update NavSync set myTimeStamp = @myRevTimestamp, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=@RecCount where TableID=9
            end
      else
            update NavSync set LastExecuted = getdate() where TableID=9
GO
0
ktjamms2
Asked:
ktjamms2
  • 58
  • 34
  • 6
7 Solutions
 
timexistCommented:
1,you need setup a database connection myDBconn(it can be new OLE DB Connection, right click  at space part under  connetion managers ).
2, you can drag and drop an Excute SQL Task, double click it, select Connection from dropdown list.( myDBconn or create a new connection.)
3, put
 exec  [dbo].[Sync_Lab_Test_PF]
in the sqlstatementsource field.
0
 
timexistCommented:
0
 
ktjamms2Author Commented:
What I would really like to do for an educational exercise is eliminate/replace the stored procedure and recreate this as an SSIS solution with error handling.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
timexistCommented:
OK, I have to say stored procedure is much fast than SSIS package, and you can put try catch inside stored procedure.

SSIS is used to transfer data, is not designed for error handling inside a database.
I think it should be regarded as error handling between databases.
I always use Script Task to handle this.

http://msdn.microsoft.com/en-us/library/ms135944.aspx
SQL Server 2008 R2 Script Task Examples
0
 
Reza RadCommented:
If you want to replace that stored procedure to an ssis package, tell us what you did exactly in the stored procedure,
and we will help you in details

0
 
ktjamms2Author Commented:
Please standby reza red.... I'm going to initially be working on development of this from my home office and right now, don't have access to the source database. I have created empty copies of the target database  tables.  I'm looking at the possibility of using Data Generator Source Adapter (which I have no experience with either). Let me know what your thoughts are about that idea.This is going to be fun and exciting and very educational! I didn't wright this stored procedure, but here is my interpretation of what the stored procedure does:

Query table NavSync where TableID=9 to set variable @myLasttime = myTimestamp
 
 if record where TableID=9 doesn’t already exist, Insert into NavSync values (9,1,getdate(),getdate(),0,'PFPosted')
and query table NavSync again  to set variable @myLasttime = myTimestamp

Query source table MYSQL.MyDatabase.dbo.[PowerFactorPosted] where [timestamp] > @myLasttime to store field values in variables (done with curosr)

update table  PowerFactorPosted with data from source database - MYSQL.MyDatabase.dbo.[PowerFactorPosted] where Customer_No = @Customer_No and TC_No = @TC_No and Draw_Date = @Draw_Date
or If no record already exists for Customer_No... etc. then insert into PowerFactorPosted

I won't be running the application that generates the input values for variables:
@Customer_No as varchar(20)
@TC_No as varchar(20)
@Draw_Date as Datetime
.....so I suppose I will have to set the values manually for development purposes

set @RecCount = @RecCount + 1
set @myRevTimestamp = @TempTimestamp

Loop through the records updating or inserting

if @RecCount > 0

update NavSync fields with current dates and record count where TableID=9

else
update NavSync with LastExecuted = current date where TableID=9

Thank-you in advance for the willingness to help me!

KT


0
 
Reza RadCommented:
Lots of to do's in this BASIC package ;)

let's start ,
first of all you should work with execute sql task to run a query to find out if there is any record with tableID=9 or not?
for this you should set an oledb connection in execute sql task,
set result set as single row
sqlstatement as select count(*) as cnt from NavSync where TableID=9
create a variable before this, of type Int32, scope package, and name it as Cnt
now in result set tab of execute sql task, set the User::Cnt variable with index 0
this will fill result of count(*) into Cnt variable.

now you should use precedence constraint to check if @Cnt >0 or not, if yes go to another execute sql task for update. if no go to insert in a third stored procedure.

let me know if you have problem in implementation this part.


0
 
ktjamms2Author Commented:
OK, so putting the connection situation  aside...assume I've successfully configured the Connection Manager. I want to begin working on what the structure should look like. I'm not sure what my first step should be. I'm thinking about configuring a Execute SQL Task with a query to count the records resulted from the query count (*) from NavSync where TableID=9 and create a precedence constraint Expression (create a package variable cnt) to direct a data flow task to update or another to insert depending on the count. But I'm not sure how to syntax the query to set my variable
count (select @myLasttime = myTimestamp from NavSync where TableID=9)
0
 
ktjamms2Author Commented:
"now in result set tab of execute sql task, set the User::Cnt variable with index 0"
My Result Set tab only has Result Name and Variable Name as properties to configure (index 0?)
0
 
ktjamms2Author Commented:
Also, if count >0 that means the record exists. I don't want to update the table but I want to set my variable
select @myLasttime = myTimestamp from NavSync where TableID=9
how can I do this? If I first create a variable myLasttime  of type DateTime, scope package, and name it as myLasttime, can I use this query within the Execute SQL Task to set the value of the variable?
0
 
Reza RadCommented:
when you configure result set in execute sql task, result of sql statement will filled in the variable, you don't need to set it inside sql statement,
just follow what I suggested to you step by step.

0
 
ktjamms2Author Commented:
I'm understanding your suggestion for setting the count variable. Once I determine that a record exists, I need to query that record to get the value of the field {myTimestamp} to use in the next query from the source data.
0
 
ktjamms2Author Commented:
so, you mean use it like this....select  myTimestamp as myLasttime from NavSync where TableID=9
0
 
Reza RadCommented:
>>so, you mean use it like this....select  myTimestamp as myLasttime from NavSync where TableID=9<<
yes exactly.
and then you can set a variable in result set to fill result of myTimestamp to it.

is it clear or not?
0
 
ktjamms2Author Commented:
Now this on the insert task:
 Error: 0xC00291E2 at Execute SQL Task 1, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
0
 
ktjamms2Author Commented:
On the Execute SQL Task Editor:Result Set...is Result Name the same as what you're calling index?
0
 
ktjamms2Author Commented:
OK, all is well with that step except my variable type for myLasttime. I set it as a DateTime and in the database it is set to binary(8), but binary(8) isn't an option in SSIS as a datatype.
0
 
ktjamms2Author Commented:
OK, I tried char and that worked :)
0
 
ktjamms2Author Commented:
So what's your next proposal :)
0
 
timexistCommented:
I am glad you finish your an educational exercise.
These two site are very good.

sql server 2005 2008 SSIS site.
http://www.sqlis.com
sql server 2000 DTS site
http://www.sqldts.com/

have fun
0
 
Reza RadCommented:
OK,

next step is :
>>Query source table MYSQL.MyDatabase.dbo.[PowerFactorPosted] where [timestamp] > @myLasttime to store field values in variables (done with curosr)<<

you should use value of variable myLasttime in this query,
so add another execute sql task like earlier ones,
set an OLEDB connection.
the only difference here is that your sqlstatement should be contains parameters, so your sql statement should be :
select f1,f2,.. from MYSQL.MyDatabase.dbo.[PowerFactorPosted] where [timestamp] > ?
in this statement f1,f2,... are field names which you want to fill them into variables in result set tab
and question mark ( ? ) is the parameter marker when you work with oledb connection managers,

then you should map the myLasttime variable to this parameter, so go to Parameter mappings tab,
set your variable in variable Name column, and set  other things like data type, size,
and set parameter name as 0
this parameter names use indexes and you should start them from 0,1,2,.. when you work with oledb connection managers,

there are other kind of connection managers and each of them has its own signature to work with parameters, this link show different signature and how to use for all connection types in execute sql task:
http://msdn.microsoft.com/en-us/library/ms140355.aspx


0
 
ktjamms2Author Commented:
What should I set for Direction - Input?  and for Parameter Size?
Also,  What is the function of Result Name in the Result Set? Is it related to the Parameter Name in Parameter Mapping, or Does it matter what the name is?
0
 
Reza RadCommented:
direction: input ( for input parameters )
size: based on parameter size, for example if your parameter size is varchar(200) in stored procedure, use 200 as size.

Result Name is exact column name for result stream, for example if your query is : select f1 from mytable
and you want to fetch f1, use f1 as result name.
0
 
ktjamms2Author Commented:
>>select f1,f2,.. from MYSQL.MyDatabase.dbo.[PowerFactorPosted] where [timestamp] > ?
in this statement f1,f2,... are field names which you want to fill them into variables in result set tab
and question mark ( ? ) is the parameter marker when you work with oledb connection managers<<

So...I'm not certain...do we want to do a parameter mapping for all the fields in my select query, or only map the myLasttime variable?
0
 
ktjamms2Author Commented:
OK, so I have created package variables for the fields,  input my SQL statement and matched my variables (Variable Name) to Result Name (field names) in the Result Set tab. I only set a parameter mapping for User::myLasttime as Input, parameter=0. (let me know if I need to do any parameter mapping for the fields).

What should be my next move?...I'm starting to have fun now! ;)
0
 
Reza RadCommented:
>>OK, so I have created package variables for the fields,  input my SQL statement and matched my variables (Variable Name) to Result Name (field names) in the Result Set tab. I only set a parameter mapping for User::myLasttime as Input, parameter=0. (let me know if I need to do any parameter mapping for the fields). <<
you need to set parameter mapping for each input parameters you have in your sql statement.

and also you should set Result Name for each result column you need to fetch in variables.


>>What should be my next move?...I'm starting to have fun now! ;)<<
times passed from start of question and there are many posts, I'm lost in steps!
tell me what did you do till now in your packages, and what else you want to do and I will help you furthur



0
 
timexistCommented:
how about you attach the ssis package here it will be much easier?
0
 
ktjamms2Author Commented:
Really sorry about the time gaps...had too much to deal with last week. Thank-you for being patient!

Now I need to us my Query from the source table:
select [timestamp], [f2], [f3], [f4],......etc
      from MYSQL.MyDatabase.dbo.[TableName]
      where [timestamp] > ?
      order by timestamp
already completed in Execute SQL Task)

This is the part that I really need help with  (done with curosr in my stored proceedure) :

 I need to loop through the records (results from select query) and if the record exists in destination table update destination table....where Customer_No = @Customer_No and TC_No = @TC_No and Draw_Date = @Draw_Date

or If no record already exists, then insert into destination table.

set @RecCount = @RecCount + 1
set @myRevTimestamp = @TempTimestamp

Loop through the records updating or inserting

if @RecCount > 0

update NavSync fields with current dates and record count where TableID=9

else
update NavSync with LastExecuted = current date where TableID=9

I appreciate so much your help!!
KT
0
 
ktjamms2Author Commented:
or if you have a more efficient suggestion for updating and inserting from the source table to the destination, that would be great!!
0
 
ktjamms2Author Commented:
Another step that I need to add...that is not in the stored procedure...is to create a table that will store the records that (for whatever reason) could not get inserted/updated to the destination.
0
 
ktjamms2Author Commented:
I'm guessing I need a Foreach Loop Container, but I don't have any experience configuring
0
 
Reza RadCommented:
the Update/Insert ( the terminiology is UPSERT )
in SSIS can be done in several ways, these two ways are most used ways:
1- with MERGE T-SQL statement,
http://technet.microsoft.com/en-us/library/bb510625.aspx

2- with lookup transformation ,
http://piers7.blogspot.com/2009/03/easy-upserts-in-ssis-2005.html
http://jahaines.blogspot.com/2009/09/sss-performing-upsert.html

I strongly recommed use first way if your source and destination are in same server. Merge statement has better performance than lookup.
after creating your merge statement, just you need to run it with execute sql task in ssis.


0
 
ktjamms2Author Commented:
Thanks reza rad for the links. I'll read over them. My source and destination will be on different servers, so you are recommending I concentrate the lookup transformation method in this instance?
0
 
Reza RadCommented:
of course,
when your source and destination are different OLEDB , try lookup,
when you work with lookup transformation in SSIS, there is a cache mode which raise your performance, for choosing appropriate cache mode for you case, read this great article from SSIS Team Blog:

http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx

0
 
ktjamms2Author Commented:
I chewed on those references for awhile....got acid stomach now :)
Please help me apply this to my situation! I'm not sure if I'm on the right track now. The last step we worked on was to create an SQL task where I did this:
 created package variables for the fields(source table),  input my SQL statement(query source table) and matched my variables (Variable Name) to Result Name (field names) in the Result Set tab. Set a parameter mapping for User::myLasttime as Input, parameter=0.

Should my next step be to attach the green arrow from the last SQL task to a data flow task and work to develop the lookup transformation method in the dataflow? Or, remove the last SQL task and configure my source in the data flow....
0
 
Reza RadCommented:
you need to do this part completely in a data flow task.
in the data flow task
add an OLEDB Source, and use your select query there,
then add a lookup transformation, and set destination table as lookup table there.
now connect match output ( green arrow from lookup transformation), to an OLEDB Command, oledb command is where you will apply update command there.
and connect not-match output from lookup to an OLEDB Destination which connects to destination table, this will perform insert on not-existence of data

does it make sense now?
0
 
ktjamms2Author Commented:
>>then add a lookup transformation, and set destination table as lookup table there<<

The lookup transformation editor appears to only have an option for source
0
 
Reza RadCommented:
>>The lookup transformation editor appears to only have an option for source<<
this option is the lookup table, which you should set your destination table there .

0
 
ktjamms2Author Commented:
Then in the Columns portion what should I do there? Do I need to drag the available Input Columns over to the Available Lookup Columns? When I did that the lower portion didn't change. What should the option for the lookup Operation be?
0
 
Reza RadCommented:
just connect joining key between two set of columns,
and then you should use match and not-match outputs of lookup.
match output to oledb command transformation
not-match output to oledb destination
0
 
ktjamms2Author Commented:
I connected the primary keyfields from Available Input Columns to Aailable Lookup Columns

What fields should I check in the Available Input Columns? Only the primary key fields for all the fields selected in my query from source, or none?

0
 
Reza RadCommented:
you can check all input columns there. this doesn't differ very much because you just want to use match or no-match output to update or insert, so check all columns
0
 
ktjamms2Author Commented:
When I check anything, then at the bottom there's:
Lookup Column
Lookup Operation
Output Alias

and in the Lookup Operation Column is an option:
add as new column or replace as column name

What should the configuration be?
0
 
Reza RadCommented:
do not select any column there, just connect joining fields and then close lookup transformation editor
0
 
ktjamms2Author Commented:
connect all joining fields or just the primary key fields?
0
 
Reza RadCommented:
just fields which participate in PK-FK relation on those tables
0
 
ktjamms2Author Commented:
OK, I connected the PK fields and didn't check any of the fields for the Available Lookup Columns. On the connection manager of the lookup transformation editor, I selected the destination database...do I use a table, or use an SQL query? Is there any other configurations necessary for the lookup?

I used match (green arrow) and not-match(red arrow) outputs of lookup.
matched output to oledb command transformation and not-match output to oledb destination

How should I configure the oledb command transformation and the oledb destination?
0
 
Reza RadCommented:
in lookup trasnformation there is no need to do anything more,
in oledb destination, just select your destination table, and map columns,
in oledb command, write your update command there,
like this:
update myTable set f1=? where id=?
now in parameter mapping tab, set input column with appropriate parameter


0
 
ktjamms2Author Commented:
OK, did all that but, Lookup has exclamation showing no rows will be sent to error outputs. Configure error or truncation dispositions to redirect rows
0
 
Reza RadCommented:
you should:

in lookup transformation editor,
in generatl tab,
set "specify how to handle rows with no matching entries" with "redirect rows to no match output"

did you do that before?
0
 
ktjamms2Author Commented:
Yes, that's how it is configured, but still has message
0
 
Reza RadCommented:
is it warning or error?
if this is warning you can overlook it
0
 
ktjamms2Author Commented:

It's a warning.

The package runs successfully, but nothing gets inserted into the destination table [PowerFactorPosted]. All the tables in the destination database are empty, so there sould not be any matches and all should go to insert. myTimestamp gets set to 1 from the insert into NavSync ( and all timestamp values in the source table should be greater than 1, so all records in the source table should get inserted.

When I check my execution results, it doesn't appear that the package ever gets to the Data Flow. I have connected a data flow task to the last SQL task in the control flow, but the last entry in the execution results is the Query task in the control flow. And, I have that warning in the data flow task in the control flow.
0
 
ktjamms2Author Commented:
I'm not sure what data type to set the variable myLasttime (timestamp). it is set as binary(8) in the database, but binary is not one of the options in SSIS. And, there appears to be two different options between the variable types in the add variable window and the Parameter Mapping configuration. It looks like this in the database field:
0x000000001405B4D0...so I tried to set it to char?
0
 
ktjamms2Author Commented:
Now I'm really frustrated...I deleted the red arrow from the lookup and now when I try to re-connect it I get a message that says this error output cannot receive any error rows. This occurs for several reasons:
Input coluns or output columns are net yet defined.
Error handling is not supported by the component.
Error handling is not configured for the component.

Do you still want to connect this error output?  OK or Cancel
0
 
Reza RadCommented:
do you have red arrow output from lookup transform?!!!!!!!!!
this is error output !
why you used it?
could you upload screenshot of your dataflow here? there maybe something wrong there
0
 
timexistCommented:
1, make a copy of  ssis package file.
2,ssis is XML file you can use notepad to open it.  remove the password etc.
3, then you can load the ssis file.
4, load SSIS image should be ok.
5, it is much easy to discuss the detail with something there.

have fun
0
 
ktjamms2Author Commented:
I opened the package file in note pad. Not sure about the rest of what your instruction is...remove password etc...load ssis file? load with what?
0
 
ktjamms2Author Commented:
how do I upload a screenshot?
0
 
ktjamms2Author Commented:
I deleted the red arrow and used the green arrow to my OLE DB Destination. My package fails. The Execution Results show Invalid Character as the cause?? Could that be because my data type settings?

0
 
ktjamms2Author Commented:
Package failure is with Data Flow/ OLE DB Source
0
 
Reza RadCommented:
you can upload screen shot with IMAGE link right under the box which you write your posts there
0
 
ktjamms2Author Commented:
how do I browse to the file?
0
 
ktjamms2Author Commented:
Here are my screen shots
20100924.docx
0
 
Reza RadCommented:
ok. every thing seems right!
could you upload another screenshot which shows when you running the package,
I want to know what tasks go green, what tasks go yellow, and what will set red .
0
 
ktjamms2Author Commented:
screen shot of failure
20100924.docx
0
 
Reza RadCommented:
when your package fialed, when data flow task go red, don't stop package, just double click on data flow task and go in data flow tab,
then get an screenshot of data flow tab in running mode and upload it here
0
 
ktjamms2Author Commented:
yes, that was the second image on that page..OLE DB didn't go red
0
 
ktjamms2Author Commented:
OK, sorry the OLE DB did go red
20100924.docx
0
 
Reza RadCommented:
problem is in oledb source,maybe your select query has problem.
copy your exact error message from progress tab to here.

0
 
ktjamms2Author Commented:
screen shot of progress report
20100924.docx
0
 
Reza RadCommented:
your error message is not obvious fully.
right click on error message, and copy it, and paste it here
0
 
ktjamms2Author Commented:
[OLE DB Source [255]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Invalid character value for cast specification".

[SSIS.Pipeline] Error: component "OLE DB Source" (255) failed the pre-execute phase and returned error code 0xC0202009.
0
 
Reza RadCommented:
OK, paste the query you wrote in oledb source here
0
 
ktjamms2Author Commented:
SELECT     [timestamp], [Customer No_], [TC No_], [Draw Date], [Outside Lab], [AROC 1242 Content], [AROC 1254 Content], [AROC 1260 Content], [Label Color], [Other AROC Content], [Total PCB Content], [PCB Class]
FROM         dbo.[Lab Test PCB Posted]
WHERE     ([timestamp] > ?)

I set the parameter mapping:
Pamameters = 0 and variables = user::myLasttime
0
 
ktjamms2Author Commented:
Here's my variables in the stored proceedure and a screen capture of my variables in SSIS:
declare @myLasttime as binary(8)
      declare @RecCount as int

      declare @myRevTimestamp as binary(8)
      declare @TempTimestamp as binary(8)

      declare @Customer_No as varchar(20)
      declare @TC_No as varchar(20)
      declare @Draw_Date as Datetime

      declare @OutsideLab as tinyint
      declare @Recommendation_Code_ID as varchar(20)
      declare @Comment_Code_ID as varchar(10)

      declare @AROC1242Content as decimal(20,5)
      declare @AROC1254Content as decimal(20,5)
      declare @AROC1260Content as decimal(20,5)
      declare @LabelColor as decimal(20,5)
      declare @OtherAROCContent as decimal(20,5)
      declare @TotalPCBContent as decimal(20,5)
      declare @PCBClass as varchar(10)
Variables.docx
0
 
ktjamms2Author Commented:
In the Parameter Mapping of the SQL task, I set the variable myLasttime to DBTIMESTAMP  type , but in the package variable configuration I set it to string type (DBTIMESTAMP wasn't an option).
None of the same options are available in the Parameter Mapping configuration that are available in the package variable configuration. Makes it hard to know what to do?
0
 
Reza RadCommented:
I can't find any wrong section yet,
upload your whole package here in this way:
right click on package name in BIDS,
select view code,
and copy whole xml code behind in a .txt file
and upload txt file here
0
 
ktjamms2Author Commented:
Do you have some suggestions first of what to set as my variable type for the  replacement of the binary data type of myLasttime?
0
 
Reza RadCommented:
>>Do you have some suggestions first of what to set as my variable type  for the  replacement of the binary data type of myLasttime?<<
I can't understand what you mean !
upload your package here for faster progress
0
 
ktjamms2Author Commented:
I'm not familiar with what this XML file contains...is there any compromising data exposed? I don't want to reveal any data about my company other than database names and field names. Will this be viewable by everyone?
0
 
Reza RadCommented:
could you  mail it to my address
a dot raad dot g at gmail dot com
0
 
ktjamms2Author Commented:
Did you get it yet?
0
 
Reza RadCommented:
yes, I got it.
tell me what variable you set in OLEDB source , parameter setting ?
if this is User::myLastTime
are you sure that the query of previous execute sql task have a result and will return not null value?
0
 
ktjamms2Author Commented:
how can I check the value returned for myLasttime in the previous SQL task?

In  the Control Flow...SQL task query I get the value:
SELECT     myTimestamp AS myLasttime
FROM         dbo.NavSync
WHERE     (TableID = 11)

and I set the parameter in that SQL task:
myLasttime output  DBTimestamp   parameter = 0 parameter size = 20
and I configured it in the result set of that SQL task:
Result Name = myLasttime and Variable Name = myLasttime

Doesn't that appear in the XML?
0
 
ktjamms2Author Commented:
I'm still learning my way around here, but it appears the problem is with the data type settings for myLasttime.... I don't know what to set them as.....
0
 
Reza RadCommented:
>>SELECT     myTimestamp AS myLasttime
FROM         dbo.NavSync
WHERE     (TableID = 11) <<
this query has no parameter marker inside,
so your output parameter setting to DBTimestamp is incorrect,
but result os this query will fill in the myLasttime variable, because you set it inside result set tab.

now just you check the query above in SSMS exactly and see is there any result returned?

>>Doesn't that appear in the XML?<<
tasks and transformation can be seen in XML you uploaded, but parameter mappings doesn't show(because of validation)



0
 
ktjamms2Author Commented:
I do get results from query in SSMS...changed query slightly:
SELECT     myTimestamp AS myLasttime
FROM         [dbname].[dbo].[NavSync]
WHERE     (TableID = 11)

but package still fails with same message in progress report:
[OLE DB Source [255]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Invalid character value for cast specification".

[SSIS.Pipeline] Error: component "OLE DB Source" (255) failed the pre-execute phase and returned error code 0xC0202009.

0
 
ktjamms2Author Commented:
I removed the parameter mapping and package fails on OLE DB Destination... get this error now:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'InNavision', table 'MyDatabase.dbo.PCBPosted'; column does not allow nulls. INSERT fails.".

the field InNavision does not allow nulls?... the stored procedure doesn't include this field in the insert. I wonder how the stored procedure doesn't fail?
0
 
ktjamms2Author Commented:
For now (to get past this hurdle), I'll just alter the table to accept nulls...this is embarrassing, not sure the syntax to alter table to allow nulls? this doesn't work:
ALTER TABLE [MyDatabase].[dbo].[PCBPosted]
 MODIFY [InNavision] null;

What should it look like?
0
 
Reza RadCommented:
you can change column to accept null in SSMS
why you want to do it with script?
0
 
ktjamms2Author Commented:
How? Right click table...go to design... put check in allow nulls...then what?
0
 
Reza RadCommented:
then go to file menu
click on Save [yourtablename]


0
 
ktjamms2Author Commented:
Yep, when I do that, it tells me that saving changes is not permitted. and the changes require the tables to be dropped and re-created.You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
0
 
Reza RadCommented:
in SSMS
go to tools menu
option
do this change as screenshot here,
and try again

1.jpg
0
 
ktjamms2Author Commented:
OK sweet, package runs successfully now, and insert to table confirmed!!

Next: I want a table to store any data that could not get upserted

and I need to get a record count for this potion of the stored procedure

if @RecCount > 0
            begin
                  update NavSync set myTimeStamp = @myRevTimestamp, LastUpdate=getdate(), LastExecuted = getdate(),RecordCount=@RecCount where TableID=11
            end
      else
            update NavSync set LastExecuted = getdate() where TableID=11
0
 
Reza RadCommented:
I suggest you to ask this in a new question.
this question made HUGE . and I think this can not be helpful for others, because lots of aspects of ssis package considered here.
if you ask new question for each isolated problem, these knowledge base will be searchable by future people.


0
 
ktjamms2Author Commented:
Thank-you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 58
  • 34
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now