Excel connection properities -

In my package I need to change the filename name for my input Excel based on a the contents of an external file. I want to read the external file, get the filename/location from this file and then dynamically set this as the property for the Excel Connection. I saw some posts related to this but did not understand which tasks I need to use and in which order.

thanks

My connection string is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

Delayed validation is True

I noticed my server name and excel file path are = to my source (=" + @.[User::ExcelFileName] + ") and if I change any of the 3 they all change.

When I run I get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Load training data [Read R013 Excel file [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Error at Load training data [DTS.Pipeline]: component "Read R013 Excel file" (1) failed validation and returned error code 0xC020801C.

Error at Load training data [DTS.Pipeline]: One or more component failed validation.

Error at Load training data: There were errors during task validation.

Error at PackageExcel [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK
-

thx

|||

Here is an expression that worked for me, exactly as entered in the Expression box. The double quotes around the Extended Properties value need to be doubled (and escaped). Note that in this case I was using a second user variable for the Extended Properties.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

-Doug

|||

Thanks for the response

When I paste in your code as is it gets changed automatically to this?:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

Doesn't like the (") in front of Provider? Any thoughts?

Tom

|||

Not sure how to explain that. But I did copy and paste exactly what appears in the Expression box in the Expression Builder dialog on my Excel Connection Manager's Expressions property...including the opening quote.

It's been a while since I ran that test when this issue first appeared in the forum, and I no longer recall all the variations that I tried.

The tricky quotes around the value of the Extended Properties argument are only required because there's more than 1 argument. If you just use "Excel 8.0" and allow the HDR argument to default to Yes, you can skip those quotes, which may well be your problem:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties=EXCEL 8.0;

-Doug

|||

I removed your variable for ext properties

have this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcellFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

still getting the error that it can't create file

Is there any way to view what's it's resolving the variable name to before it gets to the step of reading the excel file? I'm thinking that it is still not coming up w/ the correct file name. If I replace the variable w/ the hardcoded path/filename it works.

Thanks for your help

Tom

|||

I did get to see the name resolved and it looks fine

Tom

|||

I changed the variable to be the Provider instead of the Data source - this now gives me an error 'Class not Registered' - It seems like the implementation of the variable name has a syntax problem that formats it incorrectly.

Any suggestions?

Provider=" + @.[User::MyFileName] + ";

thx

|||

I got a bit lost in the thread above, but as an expression your example will not work. There is no opending quote and the semi-colon literal section is not quoted close. Maybe this forum mucks up the format but this should work-

"Provider=" + @.[User::MyFileName] + ";"

|||

Darren

when I tried to wrap the whole string w/ " the UI didn't like it and put a second 'Provider' at the end.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

In the above example I'm trying to set the data source w/ the parameter (what I really want) - when I couldn't get that to work I tried to use the parameter w/ Provider to see what would happen there.

Just to clarify what I'm doing (since I'm new to this), I'm setting the connectionstring property of my Excel connection manager

let me know if you have any other suggestions

thanks

|||

Just to be clear, you're entering this expression in the Expression Builder dialog box, right?

And in the Properties window for your Excel Connection Manager, the ConnectionString property itself is either (1) totally blank or (2) contains a complete, hard-coded, valid Jet/Excel connection string WITHOUT any of the expression syntax?

In other words, you're not entering the expression in the ConnectionString property directly?

|||

No I'm not - I had a feeling that I was doing something stupid - first time at using SSIS - I will give it a try - sorry for the confusion

Thx

Example of storing a DateTime value in a SQL Server database

Anybody have any examples of how to do this? I just want to input the current date in a database.

Thanks in advancetwo options

1) Set a default value in the table (in design view you set Getdate() in the default field)

2) In an INSERT query

 INSERT INTO MyTable (theField) VALUES (Getdate())

|||I know, I wasn't too descriptive. Thank you for your reply. I didn't know about adding
getdate() as a default value.

Actually, what I have is a new user registration form and when they submit their information I want to store it in a SQL Server database. One of the fields in the database is register_date and the format is DateTime.

Using VB.NET in an .aspx page, I don't know how to format the date to store it in the database.

I was extracting the Year, Month, and Day from a DateTime object and then concatenating them into a string to form a new date.

Example:

Public Function formatCurrentDate()
Dim curdate As DateTime
curdate = DateTime.Now
Dim day As Integer = curdate.Day
Dim month As Integer = curdate.Month
Dim dtmformattedDate As DateTime
Dim strDate As String

If day < 10
day = 0 & day
End If

If month < 10
month = 0 & month
End If

strDate = month & day & curdate.Year

dtmformattedDate = Convert.ToDateTime(strDate)

Return dtmformattedDate
End Function

This does not seem to work. I'm really new to all of this so if I'm in the wrong forum please let me know.

Thanks in advance,

CrunkDaddy|||The way a date should be formated is Month/Date/Year. If you do Date/Month/Year, I think you'll need to change a setting in SQL Server (a collation, or something like that).

Do they have to specify Register Date? If its just the date that they registered, when you send the SQL Query over, use Now.Date for the value. VB will send along the current date.

Public Function formatCurrentDate()
 Dim curdate As DateTime
 curdate = DateTime.Now
 Dim day As Integer = curdate.Day
 Dim month As Integer = curdate.Month
 Dim dtmformattedDate As DateTime
 Dim strDate As String
 If day < 10
 day = 0 & day
 End If
 If month < 10
 month = 0 & month
 End If
 strDate = month & day & curdate.Year
 dtmformattedDate = Convert.ToDateTime(strDate)
 Return dtmformattedDate
End Function

|||Really, to avoid collation problems between the webserver and the database server I tink it's better to just use GetDate()

When you live in the US you don't have those problems, I know, but if you live outside the US working with dates is really a PITA (e.g. English OS on the webserver and Dutch OS on the database server, nice...)|||CrunkDaddy, it's wasn't my intention to offend you with my 'Huh?' reply, sorry if I did. :-)|||Not offended at all. Thanks for all your help. I'll let you guys know if I get this to work.|||Hi CrunkDaddy,

The solution is very easy. To avoid any local specific formatting. Just use the ANSI format 'yyyy-mm-dd'. For instance, if you want to store July 24, 2002 in your datetime column:

INSERT INTO myTable (myDateCol) VALUES ('2002-07-24')

The value '2002-07-24' is a string. Now what you have to do in your VB or C# prg is to parse the DateTime value, excerpt the year, month and date to build this string. The ANSI format is insensitive to regional setting or specific collation.

HTH|||When inserting from a web form use this method

If u expect the user will enter the date in the format dmy
set dateformat dmy
insert into table(date_col) values (15/3/2004)

If u expect the user will enter the date in the format mdy
set dateformat mdy
insert into table(date_col) values (3/15/2004)

event id 4014

Hello,
I keep getting these errors in Event Log.
Source: MSSQLSERVER
A fatal error occurred while reading the input stream from the network. The
session will be terminated.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Has anyone seen these events? I couldn't find any information on how to fix
this error and if you know anything about it would you please share?
Thanks
Anyone?
"Zvonimir Bilic" wrote:

> Hello,
> I keep getting these errors in Event Log.
> Source: MSSQLSERVER
> A fatal error occurred while reading the input stream from the network. The
> session will be terminated.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Has anyone seen these events? I couldn't find any information on how to fix
> this error and if you know anything about it would you please share?
> Thanks
|||You have a network issue - you'd want to start by monitoring
the network activity to find out what the issue is. The
following article explains how to setup network monitoring:
How to use Network Monitor to capture network traffic
http://support.microsoft.com/?id=812953
-Sue
On Fri, 30 Mar 2007 12:06:01 -0700, Zvonimir Bilic
<cezar@.hotmail.com> wrote:
[vbcol=seagreen]
>Anyone?
>"Zvonimir Bilic" wrote:

event id 4014

Hello,
I keep getting these errors in Event Log.
Source: MSSQLSERVER
A fatal error occurred while reading the input stream from the network. The
session will be terminated.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Has anyone seen these events? I couldn't find any information on how to fix
this error and if you know anything about it would you please share?
ThanksAnyone?
"Zvonimir Bilic" wrote:

> Hello,
> I keep getting these errors in Event Log.
> Source: MSSQLSERVER
> A fatal error occurred while reading the input stream from the network. Th
e
> session will be terminated.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Has anyone seen these events? I couldn't find any information on how to fi
x
> this error and if you know anything about it would you please share?
> Thanks|||You have a network issue - you'd want to start by monitoring
the network activity to find out what the issue is. The
following article explains how to setup network monitoring:
How to use Network Monitor to capture network traffic
http://support.microsoft.com/?id=812953
-Sue
On Fri, 30 Mar 2007 12:06:01 -0700, Zvonimir Bilic
<cezar@.hotmail.com> wrote:
[vbcol=seagreen]
>Anyone?
>"Zvonimir Bilic" wrote:
>

event id 4014

Hello,
I keep getting these errors in Event Log.
Source: MSSQLSERVER
A fatal error occurred while reading the input stream from the network. The
session will be terminated.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Has anyone seen these events? I couldn't find any information on how to fix
this error and if you know anything about it would you please share?
ThanksAnyone?
"Zvonimir Bilic" wrote:
> Hello,
> I keep getting these errors in Event Log.
> Source: MSSQLSERVER
> A fatal error occurred while reading the input stream from the network. The
> session will be terminated.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Has anyone seen these events? I couldn't find any information on how to fix
> this error and if you know anything about it would you please share?
> Thanks|||You have a network issue - you'd want to start by monitoring
the network activity to find out what the issue is. The
following article explains how to setup network monitoring:
How to use Network Monitor to capture network traffic
http://support.microsoft.com/?id=812953
-Sue
On Fri, 30 Mar 2007 12:06:01 -0700, Zvonimir Bilic
<cezar@.hotmail.com> wrote:
>Anyone?
>"Zvonimir Bilic" wrote:
>> Hello,
>> I keep getting these errors in Event Log.
>> Source: MSSQLSERVER
>> A fatal error occurred while reading the input stream from the network. The
>> session will be terminated.
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> Has anyone seen these events? I couldn't find any information on how to fix
>> this error and if you know anything about it would you please share?
>> Thanks

Evenly splitting output in SSIS

I would like to split the data stream so that 1 copy can be sent to a lookup task, and the other can be sent as input to another target.

There doesn't seem to be a way to do this in SSIS; but maybe I should be considering other options?

Can anyone suggest a way to copy data to two different sources?I think you want to use the multicast component.|||Boy, I had experimented with that before and completely forgot about it. Thanks!