Solved

Basic Package

Posted on 2010-09-14
98
1,211 Views
Last Modified: 2013-11-10
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
Comment
Question by:ktjamms2
  • 58
  • 34
  • 6
98 Comments
 
LVL 4

Expert Comment

by:timexist
Comment Utility
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
 
LVL 4

Expert Comment

by:timexist
Comment Utility
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 4

Expert Comment

by:timexist
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Accepted Solution

by:
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
"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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
so, you mean use it like this....select  myTimestamp as myLasttime from NavSync where TableID=9
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
>>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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
On the Execute SQL Task Editor:Result Set...is Result Name the same as what you're calling index?
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
OK, I tried char and that worked :)
0
 

Author Comment

by:ktjamms2
Comment Utility
So what's your next proposal :)
0
 
LVL 4

Expert Comment

by:timexist
Comment Utility
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
>>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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
>>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
 
LVL 4

Expert Comment

by:timexist
Comment Utility
how about you attach the ssis package here it will be much easier?
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
or if you have a more efficient suggestion for updating and inserting from the source table to the destination, that would be great!!
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
I'm guessing I need a Foreach Loop Container, but I don't have any experience configuring
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
>>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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
>>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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
do not select any column there, just connect joining fields and then close lookup transformation editor
0
 

Author Comment

by:ktjamms2
Comment Utility
connect all joining fields or just the primary key fields?
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
just fields which participate in PK-FK relation on those tables
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
Yes, that's how it is configured, but still has message
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
is it warning or error?
if this is warning you can overlook it
0
 

Author Comment

by:ktjamms2
Comment Utility

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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 
LVL 4

Expert Comment

by:timexist
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
how do I upload a screenshot?
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
Package failure is with Data Flow/ OLE DB Source
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
you can upload screen shot with IMAGE link right under the box which you write your posts there
0
 

Author Comment

by:ktjamms2
Comment Utility
how do I browse to the file?
0
 

Author Comment

by:ktjamms2
Comment Utility
Here are my screen shots
20100924.docx
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
screen shot of failure
20100924.docx
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
yes, that was the second image on that page..OLE DB didn't go red
0
 

Author Comment

by:ktjamms2
Comment Utility
OK, sorry the OLE DB did go red
20100924.docx
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
problem is in oledb source,maybe your select query has problem.
copy your exact error message from progress tab to here.

0
 

Author Comment

by:ktjamms2
Comment Utility
screen shot of progress report
20100924.docx
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
your error message is not obvious fully.
right click on error message, and copy it, and paste it here
0
 

Author Comment

by:ktjamms2
Comment Utility
[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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
OK, paste the query you wrote in oledb source here
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
>>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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
could you  mail it to my address
a dot raad dot g at gmail dot com
0
 

Author Comment

by:ktjamms2
Comment Utility
Did you get it yet?
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 500 total points
Comment Utility
>>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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
you can change column to accept null in SSMS
why you want to do it with script?
0
 

Author Comment

by:ktjamms2
Comment Utility
How? Right click table...go to design... put check in allow nulls...then what?
0
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
then go to file menu
click on Save [yourtablename]


0
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
in SSMS
go to tools menu
option
do this change as screenshot here,
and try again

1.jpg
0
 

Author Comment

by:ktjamms2
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Closing Comment

by:ktjamms2
Comment Utility
Thank-you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now