Bulk Insert with Mirroring

Can anyone suggest a bulk insert solution in a mirroring environment?
Basically, I'd like to import a text or excel file using SSIS.
Do all the "Flat File" and "Excel" data flow sources use bulk inserts?
Just wondering if I would need to use a "staging" table or something like that since bulk inserts won't be logged when dbs are mirrored. I'm assuming I'd be able to import data using the dataflow sources into a sql destination "staging" table, then do a "INSERT INTO [production] SELECT * FROM [staging]". I'm assuming this will be logged (but slow).

Any help would be appreciated.

BULK INSERT is fully logged in FULL mode, so you should not need to do the intermediate step.

Bulk insert with child records

Hi,

Maybe this is a really silly problem, but I was wondering if anyone could help me find a solution.

On a daily basis I have to insert about a 1000 new (parent) records in table, with about 25000 corresponding (child) records.

The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.

I would like to use a bulk insert statement to insert the records for both tables, but the problem is that the foreign key in the child table is not automatically updated to the new value of the primary key in the parent table.
Does anyone know a work-around?? Maybe there's a better way of inserting the records?Originally posted by ChrisHens
The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.


If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.|||Originally posted by DoktorBlue
If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.

Yes that's exactly what I am trying to achieve. But if I am to keep my old key in the parent table, wouldn't I need an extra field? Wouldn't this also imply that after the insert of the parent and child records that I will have to somehow set the old parent key to a default value? Otherwise future joins might fail right?|||You are correct in all your points:
1) you need the old ID as an extra field, but this isn't really a problem. Often, the old ID is even functional to allow te refer to your source system.
2) if you expect to get the same parent ID in the future again, you can "flush" your old IDs after processing, or you can add some batch processing control. Options are to add a batch number (as an extra field or as unifying part of your old ID), or you can consider to make a batch control table, storing the last new ID (assuming that the identity is continue increasing) of your previous batch.

Options enough, it's depending on your situation; I'm curious what you will do.

Bulk Copy Terror

Hi there,

I'm in dire need for a solution to a "Bulk Insert" problem.

Environmental: SQL 7
The unc path used is a shared folder on the local machine with full rights.

This is the SQL Table:

CREATE TABLE [dbo].[ArgiefTransaksies] (
[TakDepotKodeRef] [char] (4) NULL ,
[HoofraamNommer] [varchar] (2) NULL ,
[Dokumentnommer] [decimal](2, 0) NULL ,
[DataLyn] [varchar] (133) NULL)

Here is the file that I try to upload, TESTFILE.PRN

RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12|This is a test|\n
RZGB|FT|12| |\n
RZGB|FT|12|Zone 1 Zone 2|\n
RZGB|FT|12|Hello World||\n
RZGB|FT|12| 5 leading spaces|\n
RZGB|FT|12| Tab(10) - Hello|\n

Here is the SQL statement that I try to use:

Exec sp_dboption 'VKBTakDev', 'select into/bulkcopy', 'TRUE'

BULK INSERT ArgiefTransaksies
FROM '\\HKDATA060XP\Public\TESTFILE.PRN'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
MAXERRORS = 0,
CODEPAGE = 'raw'
)

Here is the error that I keep getting...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Regardshttp://support.microsoft.com/default.aspx?scid=kb;en-us;q324122 to workaround the issue.|||Have you tried BCP? See what happens when you go through the BCP wizard and create a format file. If it succeeds try to use it in BULK INSERT.

Building/Running a Package.

Hi,

I have a solution, and I have a few projects in this solution. Each project has a few packages in them. The problem/question I have is this: when I am working on one of the packages, I have only this package open. When I try to run/test this package, every single package in every project gets opened, recompiled/rebuilt before my current package gets to run. It's very frastrating and time consuming. Is there a way to somehow disable this weird behavior ? Is there a way to just build the package I am currently working on ?

Please, help.

Thanks,

Victor.

This behavior occurs if you configured the project to build deployment utility - unfortunately, opening package was required for this. You may switch the deployment utility off temporary to avoid this.|||

Thank you, Michael. I'll try that.

Otherwise, I'll have to create a solution for each package separately.

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...
> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA
.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
KeyExt Key
150K
273J
375K
460A
Table 2
KeyValue
50KABC
60ADEF
75KGHI
Current Join on SMC
Key ExtKey Value
150KABC
273J(null)
375KGHI
460ADEF
Desired Join result
Key ExtKey Value
150KABC
273JOther
375KGHI
460ADEF
I've also tried updating the view but that didn't work for me either.
TIA
Bill
Bill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Build fails but no errors are returned

hi,

I moved my ssis solution from on dev machine to another.

When building the solution, visual studio keeps saying that the build failed but no errors are returned which is not really helpful...

any clues would be appreciated.

thanks

I don't believe you need to "build" anything.|||

well, the solution is built before starting the debugger process...

anyway, I've sorted my problem... errors where not displayed in the error list but I've found one returned in the output window.

It was complaining that it could not load the project files (note that I had no errors while loading the solution in VS).

I've deleted the .suo and other user files from the solution and it now works... did not realise that project file paths informations could be in the user files... I was expecting this information to be specific to solution and project files ...

|||I set my projects to never build under Tools-Options. Never had a problem.