Re: Repost - Sql Server 2005 - SSIS - nbr of input cols < nbr stored proc parameters (MSDN Universal Subscriber)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Barry,

Welcome! Please let me know if you need further assistance. :-)

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Reply-To: "frostbb" <barry.b.frost@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
| From: "frostbb" <frostbb@xxxxxxxxxxxxxxxxx>
| References: <OXqPZe74FHA.1420@xxxxxxxxxxxxxxxxxxxx>
<ECVYepD5FHA.1172@xxxxxxxxxxxxxxxxxxxxx>
<OLaLjqH5FHA.1252@xxxxxxxxxxxxxxxxxxxx>
<8Grvspd5FHA.1120@xxxxxxxxxxxxxxxxxxxxx>
| Subject: Re: Repost - Sql Server 2005 - SSIS - nbr of input cols < nbr
stored proc parameters (MSDN Universal Subscriber)
| Date: Thu, 10 Nov 2005 10:22:02 -0800
| Lines: 254
| Organization: Oregon Water Resources Dept
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <u9jo5Ni5FHA.2676@xxxxxxxxxxxxxxxxxxxx>
| Newsgroups: microsoft.public.sqlserver.dts
| NNTP-Posting-Host: 159.121.113.234
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.dts:61885
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| GREAT!!! Exactly the type of info I've been looking for. Many thanks
for
| the quick reply.
|
| I now have our production instance using the RTM backup up and running.
So
| I'll be able to give this a test soon. I'll let you know the results.
|
| Best wishes.
|
| Barry
| in Oregon
|
| "Peter Yang [MSFT]" <petery@xxxxxxxxxxxxxxxxxxxx> wrote in message
| news:8Grvspd5FHA.1120@xxxxxxxxxxxxxxxxxxxxxxxx
| > Hello Barry,
| >
| > Thank you for the detailed information. We could achieve your goal by
| > using
| > Derived collumn trnsform and OLEDB command.
| >
| > In this case, your procedure expects six parameters. Three of these are
| > coming from the flat file source, and other three like the output
| > parameter
| > and user_id can be populated by 3 variables which you can create in the
| > SSIS Package.
| >
| > Use Flat File Source -> Derived Column Transform -> OLEDB Command
| > Transform. All the variable data types, and the data types of Flat File
| > Source should match up with the expected data types of the stored
| > procedure.
| >
| > After flat file source, hook up a Derived Column Transform to add other
| > three variables from SSIS package, so that after derived column you have
| > total 6 output columns. You could right click the Derived Column
| > Transform->Variables and add three variable with proper datatype. For
| > example. newserialid as Int32 and returnmsg as string. After that, you
| > could create three new derived columns with the proper datatype.
| >
| > In the OLEDB command specify the procedure as
| > Exec [wrd_lkp_county_insert] ?,?,?,?,?,?
| >
| > You shall match the column from input to destination properly.
| >
| > On the Column Mappings tab for OLEDB command Transform, match up the
input
| > columns to the input parameters of the stored procedure.
| >
| > Hope that helps
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================
| >
| > Business-Critical Phone Support (BCPS) provides you with technical phone
| > support at no charge during critical LAN outages or "business down"
| > situations. This benefit is available 24 hours a day, 7 days a week to
all
| > Microsoft technology partners in the United States and Canada.
| >
| > This and other support options are available here:
| >
| > BCPS:
| >
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
| >
| > Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
| >
| > If you are outside the United States, please visit our International
| > Support page:
| > http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
| >
| > =====================================================
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --------------------
| > | Reply-To: "frostbb" <barry.b.frost@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
| > | From: "frostbb" <frostbb@xxxxxxxxxxxxxxxxx>
| > | References: <OXqPZe74FHA.1420@xxxxxxxxxxxxxxxxxxxx>
| > <ECVYepD5FHA.1172@xxxxxxxxxxxxxxxxxxxxx>
| > | Subject: Re: Repost - Sql Server 2005 - SSIS - nbr of input cols < nbr
| > stored proc parameters (MSDN Universal Subscriber)
| > | Date: Tue, 8 Nov 2005 07:40:58 -0800
| > | Lines: 285
| > | Organization: Oregon Water Resources Dept
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | Message-ID: <OLaLjqH5FHA.1252@xxxxxxxxxxxxxxxxxxxx>
| > | Newsgroups: microsoft.public.sqlserver.dts
| > | NNTP-Posting-Host: 159.121.113.234
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:16001
| > | X-Tomcat-NG: microsoft.public.sqlserver.dts
| > |
| > | Peter,
| > |
| > | Thanks for your reply it's very much appreciated. I suspect that
SSIS
| > is
| > a
| > | very powerful tool but I've been experiencing a tremendous amount of
| > | frustration trying to get it to function.
| > |
| > | I've attached the scripts I use to create our county lookup table, the
| > table
| > | insert proc and the delimited flat file that I've described below.
| > |
| > | I'm new to SSIS and I'm not at all sure how to take a 3 data columns
| > from
| > a
| > | flat file and 'feed' them to a stored proc that has 6 parameters. My
| > guess
| > | is that there has to be some type of translation component between the
| > flat
| > | file data source and the OleDB Command object. i.e. [OLE DB Source]
=>
| > ??
| > | => [OLE DB Command]. I've tried guessing at a few options but so far
| > I've
| > | totally failed.
| > |
| > | Any help you may be able to provide would be greatly appreciated.
| > |
| > | Thanks in advance.
| > |
| > | Barry
| > | in Oregon
| > |
| > | "Peter Yang [MSFT]" <petery@xxxxxxxxxxxxxxxxxxxx> wrote in message
| > | news:ECVYepD5FHA.1172@xxxxxxxxxxxxxxxxxxxxxxxx
| > | > Hello Barry,
| > | >
| > | > To understand the issue better, I'd like to know more details about
| > this
| > | > issue:
| > | >
| > | > 1. What do you suppose SP to do in the insert task?
| > | >
| > | > 2. What is the exact steps to reproduce the issue? Will you provide
a
| > | > sample file and schema of the destination data?
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================
| > | >
| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | > rights.
| > | >
| > | > --------------------
| > | > | Reply-To: "frostbb" <barry.b.frost@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
| > | > | From: "frostbb" <frostbb@xxxxxxxxxxxxxxxxx>
| > | > | Subject: Repost - Sql Server 2005 - SSIS - nbr of input cols < nbr
| > | > stored
| > | > proc parameters (MSDN Universal Subscriber)
| > | > | Date: Mon, 7 Nov 2005 08:24:51 -0800
| > | > | Lines: 55
| > | > | Organization: Oregon Water Resources Dept
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | Message-ID: <OXqPZe74FHA.1420@xxxxxxxxxxxxxxxxxxxx>
| > | > | Newsgroups: microsoft.public.sqlserver.dts
| > | > | NNTP-Posting-Host: 159.121.113.234
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:15970
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.dts
| > | > |
| > | > | Greetings,
| > | > |
| > | > | I'm trying to set up a SSIS package that will use columns from a
| > flat
| > | > | file and insert them into an existing table using an 'insert
stored
| > | > proc'
| > | > | for the target table. I keep getting an error that says I have to
| > | > provide
| > | > | inputs for all the stored proc parameters but the flat file only
| > | > contains
| > | > | the actual data columns ... confused and frustrated.
| > | > |
| > | > | Any help would be GREATLY appreciated.
| > | > |
| > | > | Thanks in advance,
| > | > |
| > | > | Barry
| > | > | in Oregon.
| > | > |
| > | > | THE SPECIFICS ...
| > | > |
| > | > | I have a flat file that contains the following columns
| > | > |
| > | > | county_name
| > | > | fips_code
| > | > | count_code
| > | > |
| > | > | and a stored procedure that inserts records into our lkp_county
| > table
| > | > |
| > | > | CREATE PROCEDURE wrd_lkp_county_insert
| > | > | (
| > | > | @new_serial_id int = '-1' OUTPUT,
| > | > | @return_msg char(250) = NULL OUTPUT,
| > | > | @current_userid char(8) = NULL,
| > | > | @county_name varchar(20) = NULL,
| > | > | @fips_code int = NULL,
| > | > | @county_code char(4) = NULL
| > | > | )
| > | > |
| > | > |
| > | > | When I try to set up an [OLE DB Source] ===> [OLE DB Command]
| > | > | SSIS data flow everything goes fine until the validation phase
when
| > the
| > | > | I get the error ...
| > | > |
| > | > | "Validation Error. Data Flow Task: OLE DB Command [42]:
Parameters
| > | > | are not bound. All parameters in the Sql command must be bound to
| > input
| > | > | columns."
| > | > |
| > | > | Ok, so how does one go about passing 3 columns from an input file
to
| > | > | a 6 parameter stored procedure ???
| > | > |
| > | > | I know this is a simple case but we're migrating from a Unix DB
from
| > and
| > | > | I'll be doing a lot of this type of thing with much more
complicated
| > | > tables.
| > | > |
| > | > | P.S. Am I simply expecting too much of SSIS ... is it just
basically
| > a
| > | > | simple minded graphical environment that meant to handle simple
| > | > | tasks a table copies and home office mailing list extractions ???
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

.

Relevant Pages

  • Re: Repost - Sql Server 2005 - SSIS - nbr of input cols < nbr stored proc parameters (MS
    ... All the variable data types, and the data types of Flat File ... > Microsoft Online Partner Support ... > |> Microsoft Online Partner Support ...
    (microsoft.public.sqlserver.dts)
  • RE: Spout.exe times out
    ... \par Microsoft Online Partner Support ... \par When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ... \par Just wanted to check back with you to see if changing the timeout as Wei Dong suggested has helped resolve the issue for you. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: NT and Novell to AD? Help? (Jason)
    ... Jason Tan ... Microsoft Online Partner Support ... When responding to posts, please "Reply to Group" via your newsreader so ...
    (microsoft.public.windows.server.migration)
  • RE: Windows and Exchange passwor dissues
    ... Does this problem occur on all Outlook editions or a particular edition? ... Microsoft Online Partner Support ... Windows and Exchange passwor dissues ... >> Microsoft Online Partner Support ...
    (microsoft.public.exchange2000.general)
  • Re: SSIS: best data flow transformation for converting bad dates to nu
    ... Can't use the derived column transformation as it doesn't support the ... ISDATE function. ... I could feed the error rows to a derived column which converts to null ...
    (microsoft.public.sqlserver.dts)