Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

Inserted Rows count from SSIS not like table Rows Count

Hi all

i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

any ideas

So from the error output you are getting 59 123 019 rows? How are you counting this?
What are you using to insert the rows?|||Mapping from values i get and columns on distination table|||How are you counting the rows inserted?|||

I figuer out the Reasone my lookup error output configure to ignore the error and the error was "can't insert duplicated key" i change the error output to insert duplicated rows in flat file but i don't test it yet , i will hint with updates , hope that's resolve the problem

wish me luck

Phil

I Count rows from distination table "Select Count "

|||

Hosam Abdel Wahab wrote:

Phil

I Count rows from distination table "Select Count "

I mean, how are you counting the rows inserted from within SSIS?

But, yes, it sounds like you are on the correct path to figuring out your problem.

Insert Value list doest not match column list

HI...

I need to do a simple task but it's difficult to a newbie on ssis..

i have two tables...

first one has an identity column and the second has fk to the first...

to each dataset row i need to do an insert on the first table, get the @.@.Identity and insert it on the second table !!

i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"

here is the script

INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@.@.Identity,
?,
?,
?
)

what's the problem ?

Is that a cut and paste of your query?

There is a missing space between "Reference)" & "Values" in the first insert statement.|||Yes...it's a copy past....

I did what you ask and the problem remains the same|||Then you must not have all of the parameters mapped. Looks like 9 parameters.|||But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!|||

Alexandre Martins wrote:

But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!

When you click on the Column Mappings tab, you can't map the columns accordingly?|||

No! it's showing the warning "Insert Value list doest not match column list" and not mapping...

The funny thing is.....this way don't works

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID,CustomerID,AddressTypeID,TypeDescription)

VALUES(@.@.Identity,?,?,?)

but this way

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID)

VALUES(@.@.Identity)

works perfect.....but i need the other fields....

i changed the table too to test....and with one field works....two or more "Insert Value list doest not match column list" and not mapping"

i don't know what to do....

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes

Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes